My company has a table in which the string dd/mm is stored for various entities and so I can compare it against some other dates (specifically for the year 2006 for example taking '01/04' and the trying to make it 01/04/2006* to then try and convert that to a datetime)
I've done the following:
convert(datetime, field + '/2006', 103)
so that it takes the dd/mm column and creates a dd/mm/yyyy string, to then convert it to a datetime value which I can then use in various comparisons.
However one of the entries in the original table of 5321 potential rows throws an error
Conversion failed when converting date and/or time from character string
I've tried looking for obvious ones (leap years) and days larger than 31st etc but no luck
Is there a way of getting SQL Server to return the string it tried converting into a date but then failed? Something like
select id, convert(datetime, field + '/2006', 103)
on error return 'error'
so that I get the primary id for row producing the error and can use that to track it down?
You can use TRY_CONVERT to find rows with invalid dates like:
SELECT *
FROM MyTable
WHERE TRY_CONVERT(DATE, field + '/2006') IS NULL
AND field IS NOT NULL;