How to fix 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value'
By FoxLearn 11/6/2024 9:35:23 AM 191
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
.
- Connection string odbc
- If else condition in sql server
- How to Insert Update Delete View and Search data from MySQL in C#
- How to Download and Restore Northwind database to SQL Server
- How to Download ODBC Driver for SQL Server
- Download SQL Server Management Studio (SSMS) Versions
- How to convert varchar to uniqueidentifier in SQL Server
- Connection string mysql