Dates in our DB is numeric with date format DDMMYY.
I wrote an SQL statement to select them as strings in YYYYMMDD format.
The problem lies where I fetch the century part, since the FROM DATE format is only YY.
For the time being I have hardcoded '20'. But I don't feel good when I do that. How can I convert century part of a numeric date field with format DDMMYY without hardcoding?
Given below is my SQL statement.
Field, ODATE is of type 6S 0 and holds date in DDMMYY FORMAT
I wrote an SQL statement to select them as strings in YYYYMMDD format.
The problem lies where I fetch the century part, since the FROM DATE format is only YY.
For the time being I have hardcoded '20'. But I don't feel good when I do that. How can I convert century part of a numeric date field with format DDMMYY without hardcoding?
Given below is my SQL statement.
Field, ODATE is of type 6S 0 and holds date in DDMMYY FORMAT
Code:
SELECT CASE WHEN LENGTH(TRIM(CHAR(ODATE))) = 5 THEN '20' CONCAT RIGHT(TRIM(CHAR(ODATE)),2) CONCAT SUBSTR(TRIM(CHAR(ODATE)),2,2) CONCAT '0' CONCAT LEFT(TRIM(CHAR(ODATE)),1) WHEN LENGTH(TRIM(CHAR(ODATE))) = 6 THEN '20' CONCAT RIGHT(TRIM(CHAR(ODATE)),2) CONCAT SUBSTR(TRIM(CHAR(ODATE)),3,2) CONCAT LEFT(TRIM(CHAR(ODATE)),2) END CASE FROM MYLIBL/MYFILE
Comment