Re: help with SQL date coversion

Right you are Vern.

For some reason I was thinking of an issue I had with a UDF where it was defined with a CHAR input parameter and when testing, I passed it a literal, e.g. 'ABC' and it failed because the literal is passed as VARCHAR.

I'm also curious about the intended purpose.  If the actual use is to convert a character field from a file, then all you need is

select varchar_format(to_date(myFldChar,'DDMMYY'), 'YYMMDD') from myFile;




On 8/16/2023 4:05 PM, Vern Hamberg via RPG400-L wrote:

Hi Peter

Your version works because you have used an actual string, '050923'. The OP used char(050923) = that gives a result of '50923', which is not a valid date, right?

The other reply suggested using digits - that won't work, either, it gives the result '0000050923' - again, that is not a valid date.

digits will work if the OP defines the data type of the number, as here - digits(dec(050923, 6, 0))

The issue seems related to default data types for constants - I did look it up and found this in the SQL Reference -

An/integer constant/specifies an integer as a signed or unsigned number with a maximum of 19 digits that does not include a decimal point. The data type of an integer constant is large integer if its value is within the range of a large integer. The data type of an integer constant is big integer if its value is outside the range of a large integer, but within the range of a big integer. A constant that is defined outside the range of big integer values is considered a decimal constant.

large integer is 4 bytes, 10 digits, big integer is 8 bytes, 19 digits - that's where the 5 leading zeroes came from.

Interesting problem, eh?

Cheers
Vern

On 8/16/2023 2:10 PM, Peter Dow wrote:

The problem might be the char() function. I think varchar_format is expectiing a varchar, not a char.

Both of these work:

values dec(varchar_format(to_date('150923','DDMMYY'),'YYMMDD'),6)

values dec(varchar_format(to_date('050923','DDMMYY'),'YYMMDD'),6)

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx t/

On 8/16/2023 9:46 AM, gio.cot via RPG400-L wrote:

Hi all

In a program i found this function that convert data from DDMMYY to YYMMDD
or vice versa.

If i run this :

values dec(varchar_format(to_date(char(150923), 'DDMMYY'), 'YYMMDD'),6) ;

it works

if i run this :

values dec(varchar_format(to_date(char(050923), 'DDMMYY'), 'YYMMDD'),6) ;

it DON'T work

how can i  solve this problem ? i think that the problem was caused from the
first zero in the 050923,  that came converted to blank ??!!


Thanks in advance