CONVERT to Date

Transact-SQL
11

You actually have multiple problems - the fact that the column is stored as a string (varchar) and the format that is stored is regional specific (DD/MM/YYYY).

If the language on the server is US English - the format DD/MM/YYYY will fail to convert to a valid date. US English format is MM/DD/YYYY and 13/09/2021 (September 13, 2021) cannot be converted on a US English system.

To convert that string to a valid date - you need to specify the appropriate style. You can identify any dates that are invalid using the following:

SELECT ...
  FROM yourtable
 WHERE try_convert(datetime, yourdate, 103) IS NULL;

This will identify any rows where {yourdate} cannot be converted to a valid datetime data type where the format expected is DD/MM/YYYY.

Once you have identified the bad rows - then you can determine what needs to be done to fix those values.

If possible - you can then add a computed column to the table using TRY_CONVERT to return a date or datetime data type. Persist that computed column and add an index and then your queries become:

SELECT ...
  FROM yourtable
 WHERE computed_column < DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 31, 0)

Using DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) resets the time portion to 00:00:00.000 of a datetime data type and the '+ 31' adds 31 days. This then gets you all dates and times less than 31 days from today - not including the 31st day from today.

If you are only working with dates - and not times - you can convert to a date data type and the above code will still work the same.

3 Likes