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

user_3146171 0
Viewed 831750

I have been getting the following error when running a SQL to convert my data type value from varchar to datetime.

Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I have checked the data and can't see anything to odd: Ran the following checks and all returning no results

SELECT [Date] from table where [DATe] is null
SELECT [Date] from table where [DATe] = ''
SELECT [Date] from table where LEN([date])> 10
SELECT [Date] from table where LEN([date])< 10
SELECT top 100 [Date] , SUBSTRING([date],4,2) from [table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 12
SELECT top 100 [Date] , SUBSTRING([date],1,2) from table where convert(int, SUBSTRING([date],4,2)) < 1 or convert(int, SUBSTRING([date],4,2)) > 31

Is there anything else worth looking at and maybe worth any pointers or help with this issue? Can't seem to get bottom of it.

datetime tsql sql-server
19 Answers

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.

user_23836880

You can make use of 

Set dateformat <date-format> ;

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

user_79097380

Test for year > 2079. I found that a user typo'ed 2106 instead of 2016 in the year (10/12/2106) and boom; so on 10/12/2016 I tested and found SQL Server accepted up to 2078, started throwing that error if the year is 2079 or higher. I have not done any further research as to what kind of date sliding SQL Server does.

user_7782940 
Varchar Date Convert to Date and Change the Format

Nov 12 2016 12:00 , 21/12/2016, 21-12-2016 this Query Works for above to change to this Format dd/MM/yyyy SELECT [Member_ID],[Name] , Convert(varchar(50),Convert(date,[DOB],103),103) as DOB ,[NICNO],[Relation] FROM [dbo].[tbl_FamilMember]

user_23986070

Add at the top:

SET DATEFORMAT ymd;

or whichever format you are using in your queries

user_64230620

I had similar problem, and after investigation I found, that there were very old dates in the database.

So I found that convert from date to datetime works only for dates after 1753-01-01 (included).

select CONVERT(Datetime, '1753-01-01', 103) as RESULT
--1753-01-01 00:00:00.000

select CONVERT(Datetime, '1752-12-31', 103) as RESULT
--The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
user_5744740

Just make sure that your Dates are compatible or can be run properly in your database manager(e.g. SQL Server Management Studio). For example, the DateTime.Now C# function is invalid in SQL server meaning your query has to include valid functions like GETDATE() for SQL Server.

This change has worked perfectly for me.

user_87883670

Slightly unusual cause for this issue but just in case anyone needs it. The code I was working on was using:

java.text.DateFormat.getDateTimeInstance()

to get a date formatter. The formatting pattern returned by this call changed from Java 8 to Java 9 as described in this bug report: https://bugs.openjdk.java.net/browse/JDK-8152154 apparently the formatting it was returning for me wasn't suitable for the database. The solution was to this instead:

DateTimeFormatter.ISO_LOCAL_DATE_TIME
user_3338420

I faced this similar issue. I don't think that many people will be facing the situation that I have faced. Nevertheless I still wanted to share my experience. I am working in a test environment and there were datetime data which was set to minimum datetime value 0001/01/01.This value is less than the minimum possible data for smalldatetime datatype. So, Make sure to check the Min Max value of the datatype. For smalldatetime you can refer to this page: https://docs.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

user_62228270
Related