CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Ali Hasan
Ali Hasan

Reputation: 1075

T- Sql date time conversion failed

All my columns are varchar so I used common expression table to convert [Column 0] to datetime since [Column 0] contains date in this format 2012-02-28 00:00:08.000

WITH pss AS 
(
select CONVERT(datetime, [Column 0]) as t, [Column 1], [Column 2], [Column 3], [Column 4], [Column 5], [Column 6]
from PWS where [Column 0]  like '%2/28%'
)
select *from pss where datepart(HOUR,[t]) >= '11' AND datepart(HOUR,[t]) <= '12'

I am getting the following message

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Upvotes: 0

Views: 764

Answers (3)

Moe Sisko
Moe Sisko

Reputation: 12051

If all else fails, I would use a cursor* to loop through each row, then, for each row, attempt to CONVERT the value of [Column 0] to a DateTime. Surround the CONVERT attempt with a TRY..CATCH block, then in the CATCH block, output some diagnostics so that you can tell exactly which row caused the error.

NOTE * : yes, cursors are generally to be avoided, but in this diagnostic situation I think it is useful.

Upvotes: 0

kaj
kaj

Reputation: 5251

You also need to provide the hint with CONVERT as to the structure of the date within a varchar. The ymd sequence will be recognised as 102: 

convert(datetime, '2012-02-28 00:00:08.000', 102)

See http://msdn.microsoft.com/en-us/library/ms187928.aspx for a description of CONVERT and the available datetime styles

Upvotes: 2

Yuriy Guts
Yuriy Guts

Reputation: 2220

Values for datetime data types earlier than January 01, 1753 are not permitted in SQL Server. SQL Server accepts the dates ranging from year 1753 to year 9999 only. If Column0 in your table contains dates earlier than Jan 01 1753, you might have this kind of issue.

Upvotes: 0

Related Questions