SQL Converting decimal type to date type

[eurohaus33]

Hello,

I'm trying to convert a decimal (9,0) to date from a db2 table

20150105 would be 01/05/2015
20160202 would be 02/02/2015
YYYYMMDD


not sure why its setup as 9 versus 8.

Would these values have a 0 in front since its setup for 9?

The reason is I need to calculate date ranges and I cannot simply add/subtract a number on this value for beginning/end of month values.

for example subtract 2 days from 1/1/2016 would be 12/30/2015

[Rabbit]

Use the DB2 CAST() function to convert the number to a string so you can add in the slashes to the date. Then use CAST() again to convert the newly formatted string to a date.

[eurohaus33]

Hi Rabbit,

I should have clarified I do not need to reformat the data to MM/DD/YYYY but rather convert the decimal data type to a date datatype.

I am trying to add/subtract days to the data like the example below.

SELECT VARCHAR_FORMAT( CURRENT TIMESTAMP + 10 DAYS,
'YYYYMMDD')

So far I have been able to do CAST to convert to string. I'm stuck at convert the string to a date dataype.


CAST(SETTLE_DAT E AS CHAR (8))

[Rabbit]

That's because it doesn't recognize 8 numbers in a row as a valid date format. That's why I said you need to cast it to a string so you can add in the slashes before trying to cast it to a date.