Re: help with SQL date coversion

[Peter Dow <petercdow@xxxxxxxxx>]

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