Forum Discussion
Questions about converting date
I used a function to convert character to date and then do a calculation using datediff.
,CONVERT(DATETIME,CONVERT(CHAR,cs.LastCourseActivityDate)) AS Last_Course_Activity_Datetime
,case
when m.CompletionStatusYear = 2023 then '2023-12-31 00:00:00.000'
when m.CompletionStatusYear = 2024 then '2024-21-31 00:00:00.000
else null
end as 'Census'
WHEN CompletionStatusGroup <> 'Complete - Reported' AND DATEDIFF(DAY ,last_course_activity_datetime,census)< 90 then 'In_Training'
However, for some reason, when I run the script, it gives me an error "Conversion failed when converting date and/or time". How can I fix this?
when m.CompletionStatusYear = 2024 then '2024-21-31 00:00:00.000
It should be '2024-12-31 00:00:00.000'
can you use TRY_Convert instead of convert? TRY_CONVERT (Transact-SQL) - SQL Server | Microsoft Learn
That should deliver NULL Values for non convertible cs.LastCourseActivityDate values und you can start the find the values that causes the error.
That's really to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.
