Re: help with SQL date coversion

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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