All Forums Database
sunny.j 90 posts Joined 10/10
07 Jun 2011
Coverting INTEGER value to Date format

I have a scenario where my date value is there in INTEGER and the format is 40205 .

Here 4 means 2004 and 02 is Month and 05 is Day

i wanna convert this in to Date . When try to convert it into Date it's showing an wrong year

sel cast( 40205 as date format 'DD/MM/YYYY')

Result : 05/02/1904 here instead of 2004 it is showing it as 1904 .

would you please help me in this Issue

sunny.j 90 posts Joined 10/10
07 Jun 2011

any one please help me its urgent

mjj 23 posts Joined 03/10
07 Jun 2011

sel cast((cast('200' || '40205' as char(8))) as date format 'yyyymmdd')

can work .........

sunny.j 90 posts Joined 10/10
07 Jun 2011

Thank you mjj ,But it is wrong for this format 100221

CarlosAL 512 posts Joined 04/08
07 Jun 2011

Two ways:

The obvious one:

SELECT CAST(CAST(20000000+your_date AS CHAR(8)) AS DATE FORMAT 'YYYYMMDD');

F.Ex.:

SELECT CAST(CAST(20000000+100221 AS CHAR(8)) AS DATE FORMAT 'YYYYMMDD');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(20000000+100221)
-----------------
20100221

The cool one:

SELECT CAST(1000000+your_date AS DATE);

F.Ex.:

SELECT CAST(1000000+100221 AS DATE);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(1000000+100221)
----------------
2010-02-21

HTH.

Cheers.

Carlos.

sunny.j 90 posts Joined 10/10
07 Jun 2011

Thank you for your reply carlosAL

Adeel Chaudhry 773 posts Joined 04/08
13 Jun 2011

@CarlosAL .... indeed a cool solution!! :)

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.