Coverting INTEGER value to Date format

[sunny_j]

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]

any one please help me its urgent

[mjj]

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

can work .........

[sunny_j]

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

[CarlosAL]

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]

Thank you for your reply carlosAL

[Adeel_Chaudhry]

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