CODEWITHSUNDEEP
sqldb2date-formatting
Naman Dubey
Naman Dubey

Reputation: 39

Convert Decimal to Date in DB2

I have a column in my table having date value as Decimal like 20180715 for the date 15-07-2018.

I want to convert it to MMDDYYYY format. Example: Given decimal value 20180715 is converted to 07152018.

How to do it ?

Upvotes: 2

Views: 12974

Answers (2)

KR93
KR93

Reputation: 1318

Try this query for the date

select 
date(timestamp_format(char(yourcolumn+19000000), 'YYYYMMDD')) 
from yourtable

To get the time

select 
cast( substr( right( '00' || yourcolumn, 6) ,1,2) || ':' || substr( right( '00' || yourcolumn, 6) ,3,2) || ':' || substr( right( '00' || yourcolumn, 6) ,5,2) as time) 
from yourtable

Upvotes: 1

Esperento57
Esperento57

Reputation: 17472

try somthing like this:

select 
VARCHAR_FORMAT( TIMESTAMP_FORMAT(cast(yourcolumn as varchar(8)), 'YYYYMMDD') , 'MMDDYYYY') 
from yourtable

but you you want a really date do it:

select 
DATE( TIMESTAMP_FORMAT(cast(yourcolumn as varchar(8)), 'YYYYMMDD')) 
from yourtable

Upvotes: 3

Related Questions