CODEWITHSUNDEEP
sqlsql-serverdatesql-server-2012type-conversion
Sadat Mainuddin
Sadat Mainuddin

Reputation: 309

Converting a numeric value to date time

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?

Upvotes: 11

Views: 155090

Answers (5)

Carsten Massmann
Carsten Massmann

Reputation: 28196

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

Upvotes: 16

Sadat Mainuddin
Sadat Mainuddin

Reputation: 309

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

Upvotes: 1

Mina
Mina

Reputation: 11

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

Upvotes: 1

R S P
R S P

Reputation: 1077

SELECT CONVERT(DATETIME,CONVERT(INT,date_column))

Upvotes: 0

Nikita
Nikita

Reputation: 422

6 format is: "dd mon yy" Like this: SELECT convert(datetime, '23 OCT 16', 6) Other formats will cause your error

Upvotes: 0

Related Questions