DB2 integer string to date conversion

by Darrell
(Denver,CO,USA)

I am extracting an integer from a DB2 table that serves as a date, for example 20140101 is how it is stored. we want to see it as 01/01/2014. Problem is you cannot CAST it as a date because it is an integer. We use this piece of code to get into the proper format:

SUBSTR(CAST(INDV_DOB AS VARCHAR(8)),5,2) ||'/'|| SUBSTR(CAST(INDV_DOB AS VARCHAR(8)),7,2) ||'/'|| SUBSTR(CAST(INDV_DOB AS VARCHAR(8)),1,4) AS "DOB",

So the above works but it is still not a true date and therefore I cannot do date calculations. I can dump it to Excel and reformat but I need it to be a true date in SQL. Can you help?

Comments for DB2 integer string to date conversion

Average Rating starstarstarstarstar

Click here to add your own comments

Dec 03, 2015
Rating
starstarstarstarstar
Nice post
by: jhon

I don't know much about SQL data, I will search over the internet for this problem solution and will come back to you.

Nov 09, 2015
Rating
starstarstarstarstar
DB2 integer string to date conversion
by: Henry

I had the same question. Now it is solved. I am a beginner in oracle. This website is helping me as a beginner.

Oct 27, 2015
Rating
starstarstarstarstar
DB2 integer
by: Anonymous

The clarity in your post is simply spectacular and i can assume you are an expert on this subject.

Feb 27, 2015
Rating
starstarstarstarstar
Convert integer to date
by: Anonymous

In Oracle SQL this is very easy - just use to the to_char and to_date functions. Like this:

select to_char(to_date(to_char(20140727),'yyyymmdd')+1,'ddmmyy') next_day from dual

NEXT_DAY
--------
280714

The inner to_char function converts the integer toa character string, the to_date function then converts this to a date and the other parameter is the format of the date string - in this case year, month and day. You can now do date arithemtic such as add 1 day to the date and convert it back to a character string.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.