CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayush Kumar
Ayush Kumar

Reputation: 37

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

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

Upvotes: 1

Views: 835

Answers (1)

Doug_Ivison
Doug_Ivison

Reputation: 650

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

Upvotes: 0

Related Questions