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
yanivlt777 0 Newbie Poster
stbuchok
What kind of numeric?
Is it like this (mmddyyy being 07311980)? Please give more detail into how the number represents a date.
yanivlt777 0 Newbie Poster
It's like 12320640 where the actual date after some calculation/manipulation in excel is 5-June-2011.
Any idea?
stbuchok
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 0 Newbie Poster
Yes, this is a real sample.
1-January- 1988 for example is represented by 32143.
stbuchok
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).
iamthwee
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...
iamthwee
crishlay 0 Junior Poster
use convert(datetime, yourdate, 101) for datetime convertion
u can change 101 to 102,103... etc as per your needs
fazil.tk 0 Newbie Poster
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 34 Junior Poster in Training
SELECT DATEADD(DAY, 32143, CONVERT(DATE, '1899/12/31', 111)) -- RESULT = 1988-01-02
relalimarmo 0 Newbie Poster
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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.