0

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

7
Contributors
11
Replies
13
Views
6 Years
Discussion Span
Last Post by relalimarmo
0

What kind of numeric?

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

0

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

0

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?

0

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

0

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

0

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

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

0

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

0

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)
)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.