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?
For this, simply pick Date in the Number Format box on the Home tab. To apply a format other than default, then select the cells with serial numbers and press Ctrl+1 to open the Format Cells dialog. On the Number tab, choose Date, select the desired date format under Type and click OK. Yep, it's that easy!
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With