Date function in DataWindow with decimal argument

[Former Member]

I have some datawindows with year, month, day columns that are recognised from PowerBuilder as decimal(0) (Oracle NUMBER(4)).

With PowerBuilder 10 I could add a computed field in the datawindow with the expression Date(year, month, day) without any problem.

After we migreated to PowerBuilder12.5 these computed fields are invalid now, and I have to convert these columns to int or number - i.e. Integer(string(year))

[former_member1333806 · Accepted solution]

So, my first question is, why are they invalid? You haven't given us any clues to that end.

I just created an external DW with 3 decimal(0) columns, put a compute of date(year,month,day), and while I wasn't happy that they cast the result as DateTime (I can get rid of this by adjusting the Format), the DataWindow engine allowed it and it showed the converted result (with question marks where the time should be).

This description might ring bells for someone, but I suspect you'll have to supply us with more information. Specific versions would be a good start (build number, Classic or .NET), but an idea of whether non-migrated code behaves the same in an isolated example (like above) may also help, as well as help you narrow down the cause.

Let us know,

Terry.

[Former Member · Accepted solution]

I'm not happy either, but it's expected behavior. Thus speaketh Help (12.5) regarding the DW expression function:

Although the Date function returns a date value, the whole expression is promoted to a DateTime value. Therefore, if your expression consists of a single Date function, it will appear that Date returns the wrong datatype. To display the date without the time, choose an appropriate display format.

[Former Member · Accepted solution]

I am using PowerBuilder 12.5.1 Build 4015, Classic.  And yes after you mentioning it I tried with a new datawindow and it worked.  I also doubled checked that the other datawindow had decimal{0} comlumns.

What I found out is that the problem occurs when mixing retrieval arguments with decimal columns.

I have 2 retrieval arguments of type number in the datawindow - "year" and "month", and a column "day" of type decimal{0}

A computed field with expression Date( year, month, day) gives me an error "expecting NUMBER expression".  If all arguments are retrieval arguments or all arguments are decimal columns or arguments are mixture of retrieval arguments and int columns it works fine.  Problem is mixing decimal columns and retrieval arguments.

[Former Member · Accepted solution]

The DW engine has always been less sophisticated than PowerScript at dealing with expressions. So, what Neil said and what Terry D said. And I'd consider a Date column rather than three separate - it would be a lot more efficient if you need to compare, sort, or search. And of course you can choose how it gets displayed, and have easy input with a calendar dropdown or editmask.

[Former Member]

Date() does specify integers as arguments, and did in 9.0.3 (sorry, no 10 handy). It looks like the internal conversion from Decimal isn't happening any more - I know the DW engine was rewritten to make it managed code in the .NET world. I hope you don't have too many of those to fix.Unless you specifiically need a Date value (doing RelativeDate or some such) it might be easier to just form a string in the expression.

[Former Member]

date() as a powerscript function accepts integers or string, but date() as a dw function only accepts a string argument according to the help file.

[Former Member]

Ow! Given that it's undocumented, OP is lucky that it ever worked. I've also got to wonder about the DB design - three columns versus one of type Date. Gonna be ugly if you need to sort, or search without an index, or compare, or compute an offset.

[Former Member]

This could be a problem with implicit type casting?  This maybe a  bug or simply not supported implicit type cast in PB12.5.

To be safe, just adhere to the type of the function parameters.Cast as appropriate.

[Former Member]

Works for me in 12.5.2 and 12.1

Given that the DW date() function is expecting a string as argument you should never rely on implicit conversions.