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.

Answer 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

Answer 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.

Answer 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.

Answer 4

You can make use of

Set dateformat <date-format> ;

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

Answer 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)))

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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)  

Answer 12

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]

Answer 13

Add at the top:

SET DATEFORMAT ymd; 

or whichever format you are using in your queries

Answer 14

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.

Answer 15

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

Answer 16

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.

Answer 17

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

Answer 18

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

Answer 19

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.