Dates before 1940

[Viking]

Variations of this have been asked and answered here a few times, but I guess I still don't get it...

I understand that if I use a 2-digit year, then the valid date range is 1940 - 2039. But I thought if I use a 4-digit year (YYYYMMDD), then I should be able to have all years. However, this isn't what I'm seeing...

Why does this work:

[SIZE=14px][FONT=courier new]select date(timestamp_format('19400630','YYYYMMDD'))
from sysibm/sysdummy1[/FONT][/SIZE]

But this does not (I just get the +++++++ )

[SIZE=14px][FONT=courier new]select date(timestamp_format('19390630','YYYYMMDD'))
from sysibm/sysdummy1[/FONT][/SIZE]

I'm supplying all 4 digits of the year, so why the 1939/1940 cutoff...?

Btw, my goal is to find invalid birth dates in our database that need to be corrected (they're stored as separate numeric fields CC, YY, MM, and DD), but old people with valid birth dates are being returned.

[JonBoy]

Simple answer - there isn't a cutoff on four digit years.

Dunno what you did but I just tested this and it works fine (as it should) for dates back into the 1800s.

I had to change the / to a . for my machine but it worked perfectly.

[kitvb1]

That is STRSQL. In a CA window it works fine.

[B.Hauser]

You simply need to change the date format in your SQL enhancement, to a format with a 4 digit year, i.e. ISO, EUR, USA.
Contrary to RPG SQL can handle dates outside the valid range, i.e. before 1940 for a 2 digit year format, correctly.
It simply cannot be displayed. Under the cover a date stored in a running number starting with January,1st in 4713 BC (Scalliger no).
A date format is only used to make the scaliger no readable.

Birgitta

[JonBoy]

RPG works exactly the same way in terms of the calculation Birgitta. The difference is that SQL will retain the accurate result - but display it as +++ and RPG _has_ to convert it back to a displayable form at all times and will fail on the conversion. Not sure that SQL is any better really in that you end up with an undisplayable value so its accuracy is of dubious value.

[Viking]

Ok... if I run this in IBM i Access Client Solutions Run SQL Scripts, it does give me the right answer of 1939-06-30:

[SIZE=14px][FONT=courier new]select date(timestamp_format('19390630','YYYYMMDD'))[/FONT][/SIZE]
[SIZE=14px][FONT=courier new]from sysibm/sysdummy1[/FONT][/SIZE]

STRSQL on the green screen gives me +++++ and Linoma Surveyor gives me "null" (even though I set the default date format to YYYYMMDD... I will ask them about this).

[Rocky]

Ok... if I run this in IBM i Access Client Solutions Run SQL Scripts, it does give me the right answer of 1939-06-30:

[SIZE=14px][FONT=courier new]select date(timestamp_format('19390630','YYYYMMDD'))[/FONT][/SIZE]
[SIZE=14px][FONT=courier new]from sysibm/sysdummy1[/FONT][/SIZE]

STRSQL on the green screen gives me +++++ and Linoma Surveyor gives me "null" (even though I set the default date format to YYYYMMDD... I will ask them about this).

If you're using STRSQL - do a F13 - option 1 - Change session attributes. Change the date format to*ISO, *USA, *EUR or *JIS.

The problem is that it takes the date and then fits it in the date format to be displayed - which is either *YMD or *MDY - which is restricted to the 1940-2039 window. It doesn't know what to do with a 1939 year. Change the format to one of the above and it will have no problem.

Linoma will have a similar setting.

[Viking]

Thanks Rocky. You're right, changing the session attributes date format in STRSQL, and changing the connection properties date format in Surveyor, took care of the problem. Thanks everyone.