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   gt  10 SELECT  Date  from table where LEN  date   lt  10 SELECT top 100  Date    SUBSTRING  date  4 2  from  table where convert int  SUBSTRING  date  4 2    lt  1 or convert int  SUBSTRING  date  4 2    gt  12 SELECT top 100  Date    SUBSTRING  date  1 2  from table where convert int  SUBSTRING  date  4 2    lt  1 or convert int  SUBSTRING  date  4 2    gt  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

User · Answer

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.

User · Answer

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

User · Answer

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     gt  GetDate     -- or any date

User · Answer

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

User · Answer

You can make use of   Set dateformat  lt date-format gt      in you sp function or stored procedure to get things done

User · Answer

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

User · Answer

I used ToString   on a date with mm instead of MM

User · Answer

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    lt   31 AND convert int  substring  str 4 2    gt   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    lt   31 AND convert int  substring  str 4 2    gt   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    lt   31 AND convert int  substring  str 4 2    gt   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

User · Answer

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    gt   Convert datetime  examdate 103   and  Convert datetime examdate 103   lt    Convert datetime  examdate1 103

User · Answer

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

User · Answer

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

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

User · Answer

Add at the top  SET DATEFORMAT ymd    or whichever format you are using in your queries

User · Answer

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

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

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

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

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

Examples related to sql-server

Examples related to tsql

Examples related to datetime