Convert number to Date

[yanivlt777]

Hi,
I have a table with date column where the date data is represented by a number.
I would like to view this data in a date format (dd-mm-yyyy) after running the sql statement.
Appriciate if you could let me what is the code for it.

Thanks.
Y

What kind of numeric?

Is it like this (mmddyyy being 07311980)? Please give more detail into how the number represents a date.

[yanivlt777]

It's like 12320640 where the actual date after some calculation/manipulation in excel is 5-June-2011.
Any idea?

Normally if a date is a number they are calculated based off of a starting date. For Excel it is normally January 1 1900 (there is a way to have it change to January 2 1904). I'm not sure where you are getting the number 12320640 from though. Is this real sample data?

[yanivlt777]

Yes, this is a real sample.
1-January- 1988 for example is represented by 32143.

Starting date is (in yyyy-mm-dd format) 1899-12-31, now it's just the number of days since that date (remember leap years).

I think the point is why this data wasn't stored properly into the database!

I understand most excel readers dump dates out in this format but...

http://www.cpearson.com/excel/datetime.htm

Also useful...

http://www.codeproject.com/KB/datetime/exceldmy.aspx

[crishlay]

use convert(datetime, yourdate, 101) for datetime convertion

u can change 101 to 102,103... etc as per your needs

[fazil.tk]

Hai,
I have a similar problem... here i have numeric column with values like 120908 (as yymmdd)
How to convert it to date...?
Please help...?

[Eagletalon]

SELECT DATEADD(DAY, 32143, CONVERT(DATE, '1899/12/31', 111)) -- RESULT = 1988-01-02

[relalimarmo]

The function call below converts a YYMMDD numeric value to a datetime data type. The second parameter adds leading zeros in case the date has less than 6 digits (ex: 80910 = Sep 10, 2008) then trims it back down to the 6 right-most digits.

declare @i int
set @i = 120908
select convert(
    datetime,
    right('00'+ltrim(str(@i)),6)
)