... JFY if you are working with (real) date fields, the date is under the cover a running no (Scaliger no) that starts at 01/01/4713 BC (according the documentation).
Date Formats are used to make this Scaliger No readable. The date format which is used depends on the job or the connection.
If you are using a date format with a 2 digit year, dates outside the valid range (01/01/1940 - 12/31/2039) are not displayed (or displayed as ++++++), but nevertheless they are handled correctly.
A bigger problem are the numeric dates.
SQL can only convert a valid character representation of a date into a real date.
SQL can also convert numeric dates into a real date, but the numeric date must represent the calculated number of days since 01/01/0001!
With TIMESTAMP_FORMAT (or TO_DATE) almost any representation of a character date/timestamp can be converted into a date or timestamp.
What you never should do ... and this was the problem, is to convert a numeric value (date) with VARCHAR or CHAR into a character representation. The result is left adjusted, leading *Zeros are truncated and decimal points or signs (+/-) preserved.
When using DIGITS for converting leading Zeros are preserved, but neither a decimal point nor a sign is included.
If the numeric value is in a file and has a data type (such as DEC(6, 0)) the result will be 6 characters. But when trying with a number literal with out decimal points it is interpreted as INTEGER and an Integer can have up to 10 digits. So when converting such a number with DIGITS the result will be 10 digits will be returned.
If you only want the last x digits you need the RIGHT function to cut the last x numbers.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Roger Harman
Sent: Saturday, 19 August 2023 00:37
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: help with SQL date coversion
It costs no more to do 400 as it does 20 or 100. The 2 digit year format repeat is certainly a consideration. You'd need a mechanism to discriminate centuries. Joining on a 2 digit year formatted date would definitely be problematic.
Sent from my T-Mobile 4G LTE Device
-------- Original message --------
From: John Yeung <gallium.arsenide@xxxxxxxxx>
Date: 8/18/23 11:58 AM (GMT-08:00)
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: help with SQL date coversion
On Thu, Aug 17, 2023 at 4:14 AM Alan Campin <alan0307d@xxxxxxxxx> wrote:
Then just write a quick and dirty program to write dates for 20 years
or so.
In my opinion, you might as well do a lot more. I like to do 400 years, specifically 1900 through 2299, because that's the length of a complete cycle in the Gregorian calendar (146,097 days). I like to start at 1900 because that's when Excel dates start.
I think a decent case could be made for building a table of only 100 years (36,525 days, assuming the year 2000 is included), so that formats involving 2-digit years don't repeat. I don't see much value in anything smaller than that. If you specifically want dates that are too far in the past or too far in the future to be considered invalid, test against actual cutoff dates instead of relying on being missing from the table. You'll probably want to fine-tune the cutoff dates depending on the application anyway. (20 years wouldn't be enough to cover people's birthdates, or home mortgage end dates, to take a couple of obvious examples.)
John Y.
--
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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C01%7C%7C087f854a581c40f436bd08dba01d2267%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638279819213734856%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=86O5r4VGPQFoYct8r4zvfGIClHqSJwxTK8lRlXt1NO0%3D&reserved=0<
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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C01%7C%7C087f854a581c40f436bd08dba01d2267%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638279819213734856%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=gMdSpNUEUiFQ0xmXepB%2B4jhCDe0El9mbMeAF6wF9tbQ%3D&reserved=0<
https://archive.midrange.com/rpg400-l>.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
--
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.
midrange.com
