DB2 Convert Number to Date

Question

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.

Solution 1

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')

Solution 2

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.

Comments

[mustaccio]

Which version of i Series?

[Clockwork-Muse]

Don't do this in *EUR, do it in *ISO: 'YYYY-MM-DD'.

[Michael]

@mustaccio it is version 7 r2

[Michael]

@Clockwork-Muse I don't understand your comment I'm afraid.

[jmarkmurphy]

@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.

[Charles]

A date is a date. It doesn't actually have a format till displayed to a user. The format shown to the user is configurable in whatever tool you're using. Make sure your tool is set up for dd/mm/yyyy

[Clockwork-Muse]

...yes, this is probably why it's returning nulls, but I mislike the direction this sends things.

[Clockwork-Muse]

Essentially, your answer is "set a hidden dependency" - which is liable to cause things to break. It's better to remove/explicitly provide all dependencies; say, by converting the date in *ISO format, and not *EUR to begin with.

[jmarkmurphy]

@Clockwork-Muse No, my answer is to explicitly set the dependency in the code (or the driver configuration if necessary). The code blocks are actual SQL code to be included in an RPG program on a computer running IBM i. And if you read my answer, you will see that I recommended *ISO as the preferred date format, and even then to explicitly specify it.

[Clockwork-Muse]

Yes, I know that. The problem is that you can't know what else is going to be sharing the session and expecting something other than *EUR. The session state is hidden global state, mutable by anything in the process.

[jmarkmurphy]

That is wrong. Set options only affects the program it is compiled into. It is not a global setting.

[Clockwork-Muse]

I.... hmm, what happens if you CALLP into another program with/without the options set differently than the calling program?

[Michael]

This works, but if I change to: DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'DDMMYYYY')) AS TEST2 then I get nulls again?

[mustaccio]

@Michael it's probably because your stringified date format is YYYYMMDD and not DDMMYYYY

[Michael]

ah ok. I'll have to live with it as I can't make any changes on IBM

[jmarkmurphy]

The new program will use it's own set options, or the attributes specified on the compile command (which defaults to *JOB). That is why I prefer to explicitly set *DATFMT for both RPG and SQL.

[jmarkmurphy]

Why do you need to use a date field? See my answer.

[Charles]

the last line is a bad idea, now instead of getting back a date, he's getting back a string.

[Stavr00]

The DB engine layer shouldn't be deciding how to represent dates. It's up to the calling application to do that. But the OP posted a two-part question.

[Clockwork-Muse]

Ah. It's been too long. I guess I was thinking it was passing an open session/connection object down the stack. TIL.