Converting a numeric value to date time
Solution 1
I have just found the way to do this.
First I have to covert the nvarchar to int then I have to convert it to date time. I have used following code:
Select convert(datetime, (convert (int, [date_column])), 6) as 'convertedDateTime' from mytable
Solution 2
Your date is actually a numeric value (float or integer), stored in a char column. So, you need to convert it to a numerical value (in this case, to float
) first, like:
select convert(datetime, CONVERT(float,date_column))
A value of 41547.5
will result in:
`2013-10-02 12:00:00`
The style argument, in your case 6
is only necessary when converting from or to char-types. In this case it is not needed and will be ignored.
NB: The float value is the number of days since 1900-01-01
.
e.g. select convert(datetime, CONVERT(float,9.0))
=> 1900-01-10 00:00:00
; the same as select dateadd(day,9.0,'1900-01-01')
would.
The decimal part of the number also equates to days; so 0.5
is half a day / 12 hours.
e.g. select convert(datetime, CONVERT(float,.5))
=> 1900-01-01 12:00:00
. (Here our comparison to dateadd doesn't make sense, since that only deals with integers rather than floats).
Solution 3
There is an easier way to do it as well.
select convert(date,cast (date_Column+ 19000000 as nvarchar(10)))
as date_Column_Formated
from table_Name
Sadat Mainuddin
Updated on August 11, 2022Comments
-
Sadat Mainuddin over 1 year
I am working in SQL Server 2012. My date column in a data set looks like this:
41547
. The column is innvarchar (255)
. I want to convert it to something like this:yyyy-mm-dd hh:mm:ss
(Example:2013-09-14 12:23:23.98933090
). But I can not do this. I am using following code:select convert(datetime, date_column, 6)
But this is giving following error:
Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.
What am I doing wrong?
-
Sadat Mainuddin over 10 yearsthanks. However I converted the date column to int not flot. I think this will give the same value like yours.