SQL Server error in conversion of date from string-CodePudding

NPD.CreatedOn is defined as a datetime datatype column (in SQL Server).

SELECT * 
FROM NPDMaster NPD 
WHERE DATEDIFF(MONTH, CONVERT(VARCHAR(7), NPD.CreatedOn, 126), CONVERT(VARCHAR(30), GETDATE(), 126)) <= 6

I get this error:

Conversion failed when converting date and/or time from character string.

What can I try to resolve it?

CodePudding user response：

Don't use things like DATEDIFF in the WHERE on your columns, such queries aren't SARGable and thus can (will) perform poorly. If you want rows where the date is on or after the start of the month 6 months ago then do the date logic on GETDATE()/SYSDATETIME()/etc:

SQL Server doesn't have a "start of month" function, but you can use EOMONTH and then add a day:

SELECT * 
FROM dbo.NPDMaster NPD
WHERE NPD.CreatedOn >= DATEADD(DAY, 1, EOMONTH(GETDATE(),-7));

CodePudding user response：

You don't need to convert the datetime values to text. DATEDIFF() expects datetime values as second and third argument:

SELECT * 
FROM NPDMaster NPD    
WHERE DATEDIFF(month, NPD.CreatedOn, GETDATE()) <= 6

The actual reason for the error (as is explained in the documentation), is that ...DATEDIFF implicitly casts string literals as a datetime2 type.