How to fix 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value'

For example:

Convert 31/10/2024 to datetime in sql, this is a day/month/year format, which could conflict with the default datetime format in SQL Server (mm/dd/yyyy).

You can use CONVERT with style 103 to convert it properly as shown below.

SELECT CONVERT(datetime, '31/10/2024', 103)

103 is the style code for dd/mm/yyyy format.

If the date format is dynamic, or you're working with multiple date formats, you might want to standardize the input string before converting it. You can use TRY_CONVERT (which returns NULL if the conversion fails, avoiding errors) to check if the conversion succeeds.

SELECT TRY_CONVERT(datetime, '31/10/2024', 103)

If your dates are formatted as yyyy-mm-dd, you can simply use.

SELECT CAST('2024-10-31' AS datetime)

This will correctly interpret the date in dd/mm/yyyy format and convert it to datetime.