DB2 Convert Number to Date

3
votes

For some reason (I have no control over this) dates are stored as Integers in an iSeries AS400 DB2 system that I need to query. E.g. today will be stored as:

20,171,221

Being in the UK I need it to be like the below in Date format:

21/12/2017

This is from my query: (OAORDT = date field)

Select
Date(SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4)) AS "Order Date"
from some.table

However, all I get is Nulls. If I remove the Date function, then it does work but its now a string, which I don't want:

Select
SUBSTR( CHAR( OAORDT ),7,2) ||'/' || SUBSTR(CHAR ( OAORDT ),5,2) || '/' || SUBSTR(CHAR (OAORDT ),1,4) AS "Order Date"
from some.table

How do I convert the OAORDT field to Date?

Just to update - I will be querying this from MS SQL Server using an OpenQuery

Thanks.

3
sqldatedb2ibm-midrange
Which version of i Series? – mustaccio
Don't do this in *EUR, do it in *ISO: 'YYYY-MM-DD'. – Clockwork-Muse
@mustaccio it is version 7 r2 – Michael
@Clockwork-Muse I don't understand your comment I'm afraid. – Michael
@Michael Wait, it seems there is a disconnect somewhere. What do you really need? The "I'll have to live with it" comment below seems to indicate some confusion. Date() returns a native Date value. This is a thing with an internal format that you never see, and, as far as I can tell, IBM has never published it. A Date field also has an external format and that format is controlled by the *DATFMT option. All that to say that if you want a Date value, the only format that matters is the format of the string being converted to a date. See my answer. – jmarkmurphy

3 Answers

11
votes

1) How do I convert the OAORDT field to Date?
Simplest is to use TIMESTAMP_FORMAT :

SELECT DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))

2) Being in the UK I need it to be [...] in Date format 21/12/2017 : 

SELECT VARCHAR_FORMAT(DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD')),'DD/MM/YYYY')
1
votes

Note, you didn't specify where you are doing this, but since you tagged as ibm-midrange, I am answering for embedded SQL. If you want JDBC, or ODBC, or interactive SQL, the concept is similar, just the means of achieving it is different.

Make sure SQL is using dates in the correct format, it defaults to *ISO. For you it should be *EUR. In RPG, you can do it this way:

exec sql set option *datfmt = *EUR;

Make sure that set option is the first SQL statement in your program, I generally put it immediately between D and C specs.

Note that this is not an optimal solution for a program. Best practice is to set the RPG and SQL date formats both to *ISO. I like to do that explicitly. RPG date format is set by

ctl-opt DatFmt(*ISO);

SQL date format is set by

exec sql set option *datfmt = *ISO;

Now all internal dates are processed in *ISO format, and have no year range limitation (year can be 0001 - 9999). And you can display or print in any format you please. Likewise, you can receive input in any format you please.

Edit Dates are a unique beast. Not every language, nor OS knows how to handle them. If you are looking for a Date value, the only format you need to specify is the format of the string you are converting to a Date. You don't need to (can't) specify the internal format of the Date field, and the external format of a Date field can be mostly anything you want, and different each time you use it. So when you use TIMESTAMP_FORMAT() as @Stavr00 mentioned:

DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))

The format provided is not the format of the Date field, but the format of the data being converted to a Timestamp. Then the Date() function converts the Timestamp value into a Date value. At this point format doesn't matter because regardless of which external format you have specified by *DATFMT, the timestamp is in the internal timestamp format, and the date value is in the internal date format. The next time the format matters is when you present the Date value to a user as a string or number. At that point the format can be set to *ISO, *EUR, *USA, *JIS, *YMD, *MDY, *DMY, or *JUL, and in some cases *LONGJUL and the *Cxxx formats are available.

0
votes

Since none of variants suited my needs I've came out with my own.

It is as simple as:

select * from yourschema.yourtable where yourdate = int(CURRENT DATE - 1 days) - 19000000;

This days thing is leap year-aware and suits most needs fine.

Same way days can be turned to months or years. No need for heavy artillery like VARCHAR_FORMAT/TIMESTAMP_FORMAT.