3
select CONVERT(datetime,value,103) from results

This statement is giving me a error 

"Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type text to datetime is not allowed."

VALUE column Datatype is TEXT in the RESULTS table.

How can i overcome the above problem. please help !

apomene
  • 14,282
  • 9
  • 46
  • 72
Oggu
  • 323
  • 1
  • 6
  • 18

3 Answers3

6

You can't convert from text to datetime

You must apply two convert operations.

The first: From text to varchar

The second: From varchar to datetime

So your query will become:

SELECT CONVERT(datetime,
       CONVERT(varchar(30),value), 103)
FROM results

Plus: text datatype is deprecated for new versions of Sql Server, so I strongly advice you to change (if you can) your datatype text into varchar(max).

The DDL code to apply your change is:

ALTER TABLE results ALTER COLUMN value varchar(max)
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
  • When i used the above query, i am getting a error "Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string." – Oggu Jan 24 '17 at 16:01
  • Please post the format of your date. I've used 103 as in your question you have written – Joe Taras Jan 24 '17 at 20:53
2

You need first to convert text to nvarchar. Try:

select convert(datetime, convert(varchar(30),value), 103) from results

Remarks: See How to convert text column to datetime in SQL

Community
  • 1
  • 1
apomene
  • 14,282
  • 9
  • 46
  • 72
  • When i used the above query, i am getting a error "Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string." – Oggu Jan 24 '17 at 16:00
0

To convert text to float:

CONVERT (float, CONVERT (varchar(20), text_column))
David Con
  • 422
  • 3
  • 5
  • 15