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

Question

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.

What is the data type of the date column? Could you show me the table schema, and the statement that the error occurs on please? — Spikeh, Dec 30 '13 at 11:17
Data type of date column = varchar but want to convert to datetime. — user23495, Dec 30 '13 at 11:23
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. - This is what is failing after running a command to alter the column data type. — user23495, Dec 30 '13 at 11:34
you haven't checked for non valid dates e.g. 2013-10-31 or 2013-02-30. Probably, the error you are facing, refers to that kind of problematic dates — Dalen, Dec 30 '13 at 11:43
Hi Dalen, I have done this check. The way data is set up is 31/10/2013, 30/10/2013. It is in UK format. Will this have any impact when trying to alter a column type, didnt think it would. — user23495, Dec 30 '13 at 12:07
I think I may have found the issue. It is currently set as 31/10/2013 UK format, the format I want to change this column to is YYYY/MM/DD. Is there something be easy to covert this data to this format? — user23495, Dec 30 '13 at 12:17
use `yyyy-MM-dd` format for safe conversion, `31/10/2013 is invalid in dd/MM/yyyy` format — OldTrain, Apr 20 '15 at 11:22

score 116 · Answer 1 · edited Jun 18 '19 at 08:03

116

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

edited Jun 18 '19 at 08:03

jg2703

169
4
20

answered Dec 30 '13 at 13:01

Mahe

1,372
2
12
23

2

Thanks for the help mate. I tried to convert this but still not having any luck. Could it be due to table is a varchar still or anything else that can cause this to fail? – user23495 Dec 30 '13 at 15:10
3

It would be much helpful if you post your sample data (which is in the table). In comment you said you want it in yyyy-mm-dd format. So, try this `SELECT CONVERT(char(10), GetDate(),126)`. Just replace **GETDATE()** with necessary value. – Mahe Dec 30 '13 at 15:29
1

CONVERT(**DATETIME**,'2022-05-06',120) worked for me – Michael Freidgeim May 05 '22 at 06:39

score 67 · Answer 2 · answered Sep 24 '15 at 19:49

67

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.

answered Sep 24 '15 at 19:49

Mr. C

1,652
2
16
22

2

Yeah, I've just found an expiry date of 29th February 2015 in the database I have to work with. I wonder how it got in there. I wonder how many more are in there... – Resource Oct 20 '15 at 15:26
4

Just used cast(SUBSTRING([MyDateField],1,2) as integer) > 31 and found a record with the 60th of December. Who enters this stuff, Dr Suess? – SteveCav Oct 21 '15 at 22:11
3

Thanks! This was my problem. I had some bad data in my set - 01/01/1113, haha. – Sev09 Aug 08 '16 at 22:03
3

For me, I had put the wrong format string when formatting the date to build the SQL statement. I had used `Format(DateTime.Now, "yyyymmdd")` when it should have been `Format(DateTime.Now, "yyyyMMdd")` – Jay Imerman Jan 12 '17 at 14:43
1

Argh American date conventions! Had me stumped for a while, since "26th October 2017" ie., '26-10-2017' is a perfectly valid date :) – Antimony Jan 09 '18 at 21:49

score 44 · Answer 3 · edited Sep 21 '17 at 11:08

44

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.

edited Sep 21 '17 at 11:08

mit

11,083
11
50
74

answered Oct 18 '16 at 11:18

ali

1,301
10
12

3

This helped me. Just a small correction: default language of the `server login` not `db user`. https://www.top-password.com/blog/difference-between-sql-server-logins-and-database-users/ – Baz Guvenkaya Nov 08 '18 at 04:41
2

Since it set in program code and I didn't have access to the code, I changed it from `English` to `British English` and it's worked! – vaheeds Aug 28 '19 at 08:16
1

Same for me ahd to change English to British English - Ali, you are a life saver! – david-giorgi Mar 22 '20 at 16:13
I did exactly that, I changed English to British English and it worked. – Zubeid Hendricks May 26 '21 at 13:53

score 14 · Answer 4 · edited Oct 12 '17 at 05:09

14

You can make use of

Set dateformat <date-format> ;

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

edited Oct 12 '17 at 05:09

Pang

9,564
146
81
122

answered Oct 12 '17 at 04:46

Sachin Mishra

1,125
1
16
17

1

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-2017 – arlak Apr 23 '18 at 08:06
3

This solved my problem. What I don't understand is why this starting to happen suddenly :( – VictorEspina Aug 23 '18 at 15:47

score 5 · Answer 5 · edited May 28 '14 at 10:15

5

Create procedure [dbo].[a]

@examdate varchar(10) ,
@examdate1 varchar(10)
AS
Select tbl.sno,mark,subject1,
Convert(varchar(10),examdate,103) from tbl
where 
(Convert(datetime,examdate,103)  >= Convert(datetime,@examdate,103) 
and (Convert(datetime,examdate,103) <=  Convert(datetime,@examdate1,103)))

edited May 28 '14 at 10:15

Pramod S. Nikam

4,271
4
38
62

answered May 28 '14 at 09:50

sonu yadav

51
2
1

5

Please add more description to your answer. It will help questioner in grasping more out of your answer. – Pramod S. Nikam May 28 '14 at 10:11

score 4 · Answer 6 · edited Apr 26 '22 at 02:03

this happens because sql sometimes doesn't recognize dd/mm/yyyy format. So we should always check if the input string is a valid date or not and the accordingly convert it to mm/dd/yyyy and so , i have shown below how it can be done, i have created a function to rearrange in mm/dd/yyyy from dd/mm/yyyy

select case when isdate('yourdate')=1 then CAST('yourdate' AS datetime) 
  else (select * from dbo.fn_convertdate(yourdate))

Create function dbo.fn_convertdate( @Stringdate nvarchar(29))
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
)
Begin
Declare @table table(id int identity(1,1), data varchar(255))
Declare @firstpart nvarchar(255)
Declare @tableout table(id int identity(1,1), data varchar(255))

Declare @Secondpart nvarchar(255)
Declare @Thirdpart nvarchar(255)

declare @date datetime

insert into @table
select * from dbo.fnSplitString(@Stringdate,'/')
select @firstpart=data from @table where id=2
select @Secondpart=data from @table where id=1
select @Thirdpart=data from @table where id=3
set @date=@firstpart+'/'+@Secondpart+'/'+@Thirdpart
insert into @output(splitdata) values(
@date)


return
End

Had a problematic row with date string '19610010' (format: YYYYMMDD) which caused 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value' error. SELECT CONVERT(datetime, 'yourdate') FROM [yourtable] WHERE ISDATE('yourdate')=1 saved the day :) — J Pollack, Jul 03 '20 at 10:57

score 4 · Answer 7 · edited Nov 25 '21 at 15:26

As you know this is UK format issue. You can do date conversion indirectly by using function.

CREATE FUNCTION  ChangeDateFormatFromUK
( 
   @DateColumn varchar(10)
)

RETURNS VARCHAR(10)
AS 
 BEGIN
    DECLARE @Year varchar(4), @Month varchar(2), @Day varchar(2), @Result varchar(10)
    SET @Year = (SELECT substring(@DateColumn,7,10))
    SET @Month = (SELECT substring(@DateColumn,4,5)) 
    SET @Day = (SELECT substring(@DateColumn,1,2))
    SET @Result  = @Year  + '/' + @Month + '/' +  @Day

 RETURN @Result
END

To call this function

SELECT dbo.ChangeDateFormatFromUK([dates]) from table

Convert it normally to datetime

SELECT CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) FROM TABLE

In your Case, you can do

SELECT [dates] from table where CONVERT(DATETIME,dbo.ChangeDateFormatFromUK([dates])) > GetDate()   -- or any date

score 4 · Answer 8 · edited Feb 01 '21 at 21:21

4

Add at the top:

SET DATEFORMAT ymd;

or whichever format you are using in your queries

edited Feb 01 '21 at 21:21

marc_s

732,580
175
1,330
1,459

answered Feb 01 '21 at 13:38

stephen kilonzo

97
1

This solved my problem. Somehow my SQL Server couldn't parse correctly formatted EU date from string format (dd/MM/yyyy) Thanks Stephen. Up-voted :-) – Mariusz Nov 08 '21 at 08:19
In my case, I have used SET DATEFORMAT dmy; and problem solved. – edsonlp1 Feb 08 '22 at 15:55

score 2 · Answer 9 · edited Apr 16 '15 at 23:41

I've had the same problem and determined that this issue arises because SQL Server does not perform comparisons on characters converted to integers in an identical manner. In my test, I've found that some comparisons of converted characters, such as the exclamation point, will return type conversion errors, while other comparisons of converted characters, such as the space, will be determined to be out of range.

This sample code tests the different possible scenarios and presents a solution using nested REPLACE statements. The REPLACE determines if there are any characters in the string that are not numerals or the slash, and, if any exist, the length of the string will be greater than zero, thereby indicating that there are 'bad' characters and the date is invalid.

DECLARE @str varchar(10)
SET @str = '12/10/2012'
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+': Passed Test'
    ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/10/2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string
PRINT ''
GO

DECLARE @str varchar(10)
SET @str = '12/!0/2012'
    IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
        PRINT @str+': Passed Test'
        ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/!0/2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string
PRINT ''
GO

DECLARE @str varchar(10)
SET @str = '12/  /2012'
IF convert(int, substring(@str,4,2)) <= 31 AND convert(int, substring(@str,4,2)) >= 1
    PRINT @str+': Passed Test'
    ELSE PRINT @str+': Failed Test'
GO

DECLARE @str varchar(10)
SET @str = '12/  /2012' 
PRINT 'Number of characters in ' + @str + ' that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): ' + convert(varchar(5),len(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(@str,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''), '8',''),'9',''),'/',''),' ','+'))) --replace space with a + to avoid empty string

Output:

--Output
--12/10/2012: Passed Test
--Number of characters in 12/10/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 0

--Msg 245, Level 16, State 1, Line 4
--Conversion failed when converting the varchar value '!0' to data type int.
--Number of characters in 12/!0/2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 1

--12/  /2012: Failed Test
--Number of characters in 12/  /2012 that are not numerals or a slash (0 means the date is valid; all values greater than 0 indicate a problem): 2

score 2 · Answer 10 · answered Dec 24 '15 at 12:19

I too encountered this issue while auto inserting a sysdate to a column.

What I did is I changed my system date format to match with SQL server's date format. e.g. my SQL format was mm/dd/yyyy and my system format was set to dd/mm/yyyy. I changed my system format to mm/dd/yyyy and error gone

-kb

score 2 · Answer 11 · answered Oct 12 '16 at 17:05

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.

score 2 · Answer 12 · answered Nov 26 '21 at 10:04

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.

score 1 · Answer 13 · edited Feb 10 '16 at 23:14

I simply converted the varchar field that I wanted to convert into a new table (with a DateTime filed) to a DateTime compatible layout first and then SQL will do the conversion from varchar to DateTime without problems.

In the below (not my created table with those names !) I simply make the varchar field to be a DateTime lookalike if you want to:

update report1455062507424 
set [Move Time] = substring([Move Time], 7, 4) + '-'+ substring([Move Time], 4, 2) + '-'+ substring([Move Time], 1, 2) + ' ' + 
    substring([Move Time], 12, 5)

score 1 · Answer 14 · answered Oct 07 '16 at 05:07

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]

score 1 · Answer 15 · answered Jul 24 '17 at 07:33

This error occurred for me because i was trying to store the minimum date and time in a column using inline queries directly from C# code.

The date variable was set to 01/01/0001 12:00:00 AM in the code given the fact that DateTime in C# is initialized with this date and time if not set elsewise. And the least possible date allowed in the MS-SQL 2008 datetime datatype is 1753-01-01 12:00:00 AM.

I changed the date from the code and set it to 01/01/1900 and no errors were reported further.

score 0 · Answer 16 · answered Aug 30 '17 at 15:17

0

I used ToString() on a date with mm instead of MM.

answered Aug 30 '17 at 15:17

L0uis

703
5
8

score 0 · Answer 17 · answered Oct 17 '17 at 06:56

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.

score 0 · Answer 18 · answered Sep 16 '20 at 14:19

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

score 0 · Answer 19 · answered Apr 09 '21 at 09:23

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://learn.microsoft.com/en-us/sql/t-sql/data-types/smalldatetime-transact-sql?view=sql-server-ver15

score 0 · Answer 20 · answered Jun 26 '23 at 06:37

The error message you're encountering indicates that there is a problem converting a varchar data type to a datetime data type, resulting in an out-of-range value. It seems like you have already performed several checks to identify any potential issues with the data, such as checking for null values, empty strings, length, and individual date components.

Here are a few additional suggestions to investigate and resolve the issue:

Check the date format: Verify that the date values in the varchar column are in the expected format. The format should match the format expected by the SQL Server's regional settings. For example, if the date format is "MM/dd/yyyy," ensure that the data matches this format.

Handle date separators: Confirm that the date separators (e.g., "/", "-", ".") in the varchar column match the expected format. If the separators are different or inconsistent, it may cause the conversion error. You may need to perform data cleansing or use string manipulation functions to adjust the date values before conversion.

Identify specific problematic values: If possible, try to identify the specific values causing the error. You can narrow down the search by gradually reducing the dataset and focusing on specific rows or values that trigger the conversion error.

Handle potential regional differences: Be aware of regional differences in date formats, such as the order of day, month, and year. Ensure that your SQL Server settings match the expected format.

You can utilize dbForge Studio's SQL editing and debugging capabilities to review and modify your queries as needed.

This is AI-generated text (e.g., like from ChatGPT), which is banned on Stack Overflow. You should delete this before you get into even more trouble, because we take plagiarism seriously here. — tchrist, Jun 28 '23 at 02:05

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

20 Answers20

Linked

Related