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

Solution 1:

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

Solution 2:

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.

Solution 3:

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.

Solution 4:

You can make use of

Set dateformat <date-format> ;

in you sp function or stored procedure to get things done.