none
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Question

  •  

    In a query...i get this error.  How to fix this error?.

    "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

     

    Thanks

     

     

    Friday, October 03, 2008 1:47 PM

Answers

  • Always use ISO formats for dates, in your case YYYYMMDD not YYYY-MM-DD

     

    so this

     

    Code Snippet

    select name,startdate,enddate
    from course
    where startdate >= '20080901'

    and startdate <= '20080931'

    order by startdate

     

     

     

    you could also do this first

     

    Code Snippet
    SET DATEFORMAT YMD

     

     

    If the date is stored in a varchar column the use the ISDATE() function to find the bad ones

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks

    Friday, October 03, 2008 3:22 PM
    Moderator
  • You are trying to convert an invalid datetime string to a date value (may be inserting to a column or updating the value). Make sure that the date values are in the correct format (months are not more than 12 etc).

     

    Some times it can happen that you have DMY date format and the string is in MDY format. Check if that is the case. If so, issue SET DATEFORMAT MDY or DMY etc before performing the operation.

     

    If that does not help, post the actual values and operation you are performing. That will give us a clear idea of what is going wrong.

     

    Friday, October 03, 2008 1:49 PM
    Moderator
  • Try changing this:

     

    and startdate <= '20080931'

     

    to this:

     

    and startdate <= '20080930'

     

    Only 30 days in September.

     

    Friday, October 03, 2008 7:36 PM
  • Re-read the previous suggestions. To avoid ambiguity, use '20091001' and '20091031' for your dates.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    Sunday, November 01, 2009 5:49 PM
    Moderator

All replies

  • You are trying to convert an invalid datetime string to a date value (may be inserting to a column or updating the value). Make sure that the date values are in the correct format (months are not more than 12 etc).

     

    Some times it can happen that you have DMY date format and the string is in MDY format. Check if that is the case. If so, issue SET DATEFORMAT MDY or DMY etc before performing the operation.

     

    If that does not help, post the actual values and operation you are performing. That will give us a clear idea of what is going wrong.

     

    Friday, October 03, 2008 1:49 PM
    Moderator
  • the date you are trying to conver may be invalid for example:

     

    Code Snippet
    select
    cast('0/1/2008' AS DATETIME)

     

     

     

    gives the following error

     

    Msg 242, Level 16, State 3, Line 1

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

     

    check whether the dates are valid.

    Friday, October 03, 2008 1:52 PM
  • Thanks..

     

    How to fix this error in query?.

     

     

    This error is not happening for every month.

     

    For some month, i get this error...

    How to fix?.

     

    I think, in place of date...date is stored as character?.

    So do i need to covert character date to date datatype?. This will fix?. How to do this convertion?.

     

     

    Friday, October 03, 2008 2:04 PM
  •  

    Can you post some sample date you are converting?
    Friday, October 03, 2008 2:07 PM
  • As I mentioned earlier, there could be two cases (at least).

    1. the date itself is invalid (say 0 in month, day etc)

    2. month and days are not in the correct position (say your date fromat is british and the date looks like '9/13/2008') etc

     

    If the problem is #2 then you can fix it by setting the correct date format using the command I mentioned earlier.

    if the case is #1, you cant really correct the row. One option is to ignore such rows or assume a default value. Use the funtion ISDATE() to see if the date is correct. If it returns 0, that means the value is invalid.

     

    Again, we cannot help you better unless you show some sample data and mention what operation you are trying to perform.

     

    Friday, October 03, 2008 2:10 PM
    Moderator
  • Please see......if i run this query...i get result...

     

    select name,startdate,enddate
    from course
    where startdate >= '2008-09-01'

    order by startdate

     

     

    but if i run this query...i get this error"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

     

    select name,startdate,enddate
    from course
    where startdate >= '2008-09-01'

    and startdate <= '2008-09-31'

    order by startdate

     

     

    I checked DB date...Date is in this format in DB.

    2008-09-01 00:00:00.000
    2008-09-01 00:05:00.000
    2008-09-01 00:05:00.000
    2008-09-01 00:05:00.000
    2008-09-01 00:15:00.000
    2008-09-01 00:20:00.000
    2008-09-01 01:00:00.000

     

    How to fix?

     

    Friday, October 03, 2008 2:37 PM
  • Always use ISO formats for dates, in your case YYYYMMDD not YYYY-MM-DD

     

    so this

     

    Code Snippet

    select name,startdate,enddate
    from course
    where startdate >= '20080901'

    and startdate <= '20080931'

    order by startdate

     

     

     

    you could also do this first

     

    Code Snippet
    SET DATEFORMAT YMD

     

     

    If the date is stored in a varchar column the use the ISDATE() function to find the bad ones

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks

    Friday, October 03, 2008 3:22 PM
    Moderator
  • Thanks...

     

    I run this query.....

     

    select name,ISDATE(startdate),ISDATE(enddate)
    from course
    where startdate >= '2008-09-01'

    order by startdate

     

     

    I get 1 for startdate and enddate. So that means..all are date.

     

     

    Friday, October 03, 2008 4:08 PM
  • see my previous posts about using ISO formats in the WHERE clause

     

    Denis The SQL Menace

    SQL Server Programming Hacks

    ASP.NET hacks

     

    Friday, October 03, 2008 4:16 PM
    Moderator
  • Try changing this:

     

    and startdate <= '20080931'

     

    to this:

     

    and startdate <= '20080930'

     

    Only 30 days in September.

     

    Friday, October 03, 2008 7:36 PM
  • September month is always 30 days. Sometime we go mad cause of silly mistakes

    Code Snippet

    select name,startdate,enddate
    from course
    where startdate >= '2008-09-01'

    and startdate <= '2008-09-31'

    order by startdate

     

     

    Saturday, October 04, 2008 2:43 PM
  • I get the error with sql statement below:

    SELECT * FROM TABLE WHERE DATE_TIME BETWEEN '01/10/2009' AND '31/10/2009'

    BUT, it work fine with statement below:

    SELECT * FROM TABLE WHERE DATE_TIME BETWEEN '2009-10-01' AND '2009-10-31'
    Saturday, October 31, 2009 3:30 PM
  • Re-read the previous suggestions. To avoid ambiguity, use '20091001' and '20091031' for your dates.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    Sunday, November 01, 2009 5:49 PM
    Moderator