Where Clause Gives Error:“The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.”

1
votes
SELECT SchoolID
FROM   ParameterDetails
INNER JOIN
EstablishmentParameterValues AS v
ON v.ParameterID = ParameterDetailID
WHERE  NameResourceKey = 'NonTeachingStaffStartTime'
AND DATEDIFF(mi, CONVERT (DATETIME, (CONVERT (NVARCHAR (20), CONVERT (DATE, GETDATE()), 110) + ' ' + v.ParameterValue)), GETDATE()) <= 60

Above query gives the error

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

When I use write 

DATEDIFF(mi, CONVERT (DATETIME, (CONVERT (NVARCHAR (20), CONVERT (DATE, GETDATE()), 110) + ' ' + v.ParameterValue)), GETDATE()) <= 60

in Select Clause it does not give error, then why is it giving error in Where Clause..

V.ParameterValues table contains below kind of values:-

8:45
8:45
08:40:00
08:30:00
8:45
1
sqlsql-serversql-server-2008
Can you say What is v.ParameterValue value? – Vignesh Kumar A
If there are other values in ParameterValue which don't resemble times (maybe for something other than NonTeachingStaffStartTime) then you may be doomed until you split this into two separate queries. There's no guarantee on what order WHERE clause arguments are evaluated, and even if you e.g. moved part of this into a subquery, there's always SQL Server should not raise illogical errors to inform you that that strategy won't protect you. – Damien_The_Unbeliever
Did the below help, or are you still seeing an issue? – Doug_Ivison

1 Answers

0
votes

It looks like you have a mis-placed parenthesis... and the T-SQL message has nothing to do with your data. Try changing:
CONVERT (NVARCHAR (20), CONVERT (DATE, GETDATE()), 110), to
CONVERT (NVARCHAR (20), CONVERT (DATE, GETDATE(), 110)),
so that the 110 is inside that parenthesis.

If that doesn't fix your problem, you might try adding a TOP 1 (like SELECT TOP 1...) to confirm that your basic code works (at least with the first row).

You also could check for values that are not converting properly with your current logic: use the ISDATE function, which "Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0":
WHERE ISDATE( CONVERT(DATE,GETDATE(),110) + ' ' + v.ParameterValue ) = 0

Hope that helps...