sql server – SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

sql server – SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I have faced the same problem a week ago.
The problem is with the time zone setting. Specify in other formats like mm/dd/yyyy (usually works).

Specifying the date as 30/12/2013 resulted in the error for me. However, specifying it as mm/dd/yyyy format worked.

If you need to convert your input the you can try looking into the CONVERT method.
Syntax is

CONVERT(VARCHAR,@your_date_Value,103)

CONVERT(VARCHAR, 12/30/2013, 103)

The finishing 103 is the datetime format.

Refer this link for conversion formats and further reading.
https://www.w3schools.com/sql/func_sqlserver_convert.asp

I ran into this issue due to a silly mistake. Make sure the date actually exists!

For example:

September 31, 2015 does not exist.

EXEC dbo.SearchByDateRange @Start = 20150901 , @End = 20150931

So this fails with the message:

Error converting data type varchar to datetime.

To fix it, input a valid date:

EXEC dbo.SearchByDateRange @Start = 20150901 , @End = 20150930

And it executes just fine.

sql server – SQL – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I had similar issue recently. Regional settings were properly setup both in app and database server. However, execution of SQL resulted in

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

The problem was the default language of the db user.

To check or change it in SSMS go to Security -> Logins and right-click the username of the user that runs the queries. Select properties -> general and make sure the default language at the bottom of the dialog is what you expect.

Repeat this for all users that run queries.