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

Recommended Answers

What kind of numeric?

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

Jump to Post

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 …

Jump to Post

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).

Jump to Post

All 11 Replies

Member Avatar

What kind of numeric?

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

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

Member Avatar

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?

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

Member Avatar

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).

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

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

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...?

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

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)
)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.