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()) <= 60Above 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()) <= 60in 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条答案
按热度按时间bsxbgnwa1#
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), toCONVERT (NVARCHAR (20), CONVERT (DATE, GETDATE(), 110)),so that the
110is inside that parenthesis.If that doesn't fix your problem, you might try adding a
TOP 1(likeSELECT 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
ISDATEfunction, which " Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0 ":WHERE ISDATE( CONVERT(DATE,GETDATE(),110) + ' ' + v.ParameterValue ) = 0Hope that helps...