RE: help with SQL date coversion

To get around the digits() issue, I used to wrap it in right(digits(<whatever>),<how many digits you want>).
It seems pretty easy to decipher.


Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern Hamberg via RPG400-L
Sent: Wednesday, August 16, 2023 4:06 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: help with SQL date coversion

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

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.