Search code examples
sqldatedb2ibm-midrange

DB2 Convert Number to Date

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