Converting a numeric value to date time

Question

I am working in SQL Server 2012. My date column in a data set looks like this: 41547. The column is in nvarchar (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?

Answer 1

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