2
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,086
  • 9
  • 43
  • 68
Oggu
  • 313
  • 1
  • 5
  • 17

3 Answers3

5

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
  • 14,775
  • 7
  • 39
  • 53
  • 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,086
  • 9
  • 43
  • 68
  • 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
  • 353
  • 1
  • 4
  • 14