SQL Server Not Casting String YYYYMMdd to Date-CodePudding

I have a query that was working fine before a server migration and now is not working. I'm trying to convert all dates to a specific month and year, but I keep getting this error:

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

Looking into the data, there are no null values in InputDate, which is a date data type column. When I run the Concat() function everything is formatted as 'YYYYMMdd', yet both CAST and CONVERT fail with the same error.

Is there an issue with my query that I'm not seeing?

SELECT RandoSTUFF,
       DATEADD(day,2,CAST(CONCAT('2023','02',FORMAT(InputDate,'dd')) AS date)) AS MovedDate 
FROM a_table_

CodePudding user response：

I expect the issue is you have date values near the end of their months, and you're trying to compose the equivalent values for February, which is shorter.

So if you have an InputDate value of, say, 2022-12-31 and run the code in the question, it will extract the 31 and concat it with the other values, and you'll end up trying to do this:

CAST('20230231' as Date)

Of course, there is no such date.

As it is, it's not clear whether you want such an input to map to February 28 or March 3. To fix this, you'll need to rethink the problem so you only try to map to valid dates, and ensure the final result is more clearly defined. This is one of the many reasons it's almost always better to use Date/time functions instead of composing dates from strings.