none
Conversion failed when converting date and/or time from character string. SQL 2008

    Question

  • The below simple query giving me the "Conversion failed when converting date and/or time from character string." error. Because I've 
    datatype of "Created" & "Finished" column in below table VARCHAR(100)

    select * from dbo.mytable 
    where 
    CONVERT(datetime,Created) >= convert(datetime,'9/1/2010')
    and CONVERT(datetime,Finished) <= convert(datetime,'9/9/2010')
    


    In my table i've date stored in below varchar(100) formats. examples:


    '2010-09-01 11:14:54.000' --Created
    '2010-09-03 13:43:37.000' --Finished
    


    I tried to convert the date in following way but NO luck??? Could you please advise me???


    convert(datetime,convert(varchar,convert(datetime,Created),101)) >= convert(datetime,'2010-09-01') 
    and convert(datetime,convert(varchar,convert(datetime,Finished),101)) <= convert(datetime,'2010-09-09')
    



    Much appreciated your help!!



    thanks,
    pbrathod


    pbrathod
    Friday, September 10, 2010 3:55 AM

Answers

  • Your date and time values are currently stored in the created and finished attributes as character strings using the ODBC canonical representation (with milliseconds) 'yyyy-mm-dd hh:mi:ss.mmm'. Unfortunately, even though this form is in accord with standard SQL, for historical reasons, in SQL Server it is language dependent. This means that depending on the connecting login's language setting, it can be interpreted differently, due to different dateformat interpretations by each language. Take a string such as '2010-02-13 00:00:00.000' as an example. Under us_english, the 02-13 part is interpreted as month-day (due to dateformat mdy), and hence the conversion works successfully. Try it:

    set language us_english;
    select cast('2010-02-13 00:00:00.000' as datetime);

    But under, say, British, it is interpreted as day-month (due to dateformat dmy), and hence the following code fails:

    set language British;
    select cast('2010-02-13 00:00:00.000' as datetime);

    In the long run, the way to deal with this is to ensure that you store your character strings in language-neutral form, e.g., 'yyyymmdd hh:mi:ss.mmm' - note there are no dashes between the date components. Or better yet, as datetime. In the short run, use the convert function and explicitly indicate the number of the style used in the character strings (documented in BOL). As mentioned, the style you're currently using is the ODBC canonical representation (with milliseconds). SQL Server uses the number 121 to represent this style. Therefore, your code should look something like:

    select *

    from ( select

             convert(datetime, created, 121) as Created1,

             convert(datetime, finished, 121) as Finished1,

             *

           from userResults ) as d

    where Created1  >= '20100901'

      and Finished1 <= '20100909';

     

    Several important points to notice:

    1. When converting the attributes created and finished to datetime, the code uses the convert function explicitly and indicates style 121, meaning that the conversion is not dependent on the user's language but rather on the meaning of style 121 (ODBC canonical with milliseconds).

    2. Once converted to datetime in the derived table, there's no need to convert again those attributes to datetime in the outer query.

    3. When specifying literals, I use the language neutral form 'yyyymmdd' and with this form you can safely rely on implicit conversion in this language neutral form. 

    Cheers

     


    -- BG
    • Proposed as answer by Naomi NModerator Tuesday, September 14, 2010 3:11 AM
    • Marked as answer by Ai-hua Qiu Friday, September 17, 2010 7:00 AM
    Tuesday, September 14, 2010 2:18 AM
  • what is the result of below queries??

    select top 1 *
    from (select GETDATE() as Created)t 
    where Created >= convert(datetime,'2010-09-01 11:14:54.000')
    
    select convert(datetime,'2010-09-01 11:14:54.000')
    
    
     select CAST( created as datetime) as Created , CAST( Finished as datetime) as Finished into #temp
    from userResults 
    where isdate(Created) = 1 and isdate(Finished) = 1
    
    select * from #temp where Created >= convert(datetime,'2010-09-01 11:14:54.000')
    
    
    • Marked as answer by Prashantr Monday, September 20, 2010 8:10 PM
    Monday, September 13, 2010 2:44 PM

All replies

  • Those date strings should be parseable.  Instead of using convert, try using cast: cast(created as datetime) >= '2010-01-09'

    There is no need to convert the string literal... SQL Server will do it for you.

    If that still doesn't work, then you may have some bad data.  Assuming that mytable has a key called my_key, you can use code like this to identify the bad rows:

    declare @tbl table (my_key int, dt_string varchar(100))
    declare @key int
    declare @dtstr varchar(100)
    declare @dt datetime
    declare crsr cursor for select my_key,created from my_table
    open crsr
    fetch crsr into @key,@dtstr
    while(@@fetch_status=0)
    begin
     begin try
      set @dt = cast(@dtstr as datetime)
     end try
     begin catch
      insert into @tbl values (@key,@dtstr)
     end catch
     fetch crsr into @key,@dtstr
    end
    close crsr
    deallocate crsr
    select * From @tbl
    

    Friday, September 10, 2010 5:36 PM
  • seems to be there are some invalid entries are there..... the below query will gives that rows..

    check that rows and findout what is the problem.

     

    select * from mytable 
    where isdate(created) = 0 or isdate(Finished) = 0 

     

    if you wants to just omit those invalid rows and proceed, then u can use this query.

    ;with cte as
    (
    select CONVERT(datetime,Created) as Created,
    CONVERT(datetime,Finished) as Finished
     from mytable 
    where isdate(created) = 1 and isdate(Finished) = 1
    )
    select * from cte where Created >= '9/1/2010' and Finished <= '9/9/2010'
    

    Friday, September 10, 2010 5:46 PM
  • thanks ramireddy.

     

    as per your suggestion the first query did NOT return any rows.  -- all records looks good, i double check by using 1 instead 0 and its returning all available rows :)

    The second query giving me the same error : "Conversion failed when converting date and/or time from character string."

     

    --If i am selecting while converting its working fine. But when I am using Created & Finished in logical condition (<= , >=) its NOT working

     

    select convert(datetime,created),convert(datetime,Finished) from myTable --Working fine
    

     

    Thanks again in advance for your help!!

     


    pbrathod
    Friday, September 10, 2010 10:04 PM
  • its surprising... Casting all records to datetime query is working fine........ 

    Then in where clause, u r just converting 2 dates to datetime.... they are also fine....(Not even dd/mm/yyyy or mm/dd/yyyy problem also will come..)



    Can you dig it much more deeper to find out the point that causing trouble. first try to check with just 1 record by adding top 1 clause in the CTE (in the query i gave)....... if its not giving any error, increase that size of records and check until you gets the error and check that record... (suppose if u didn't have any error for top 50 and error for top 51, then check that 51st record).



    if you are getting the error with even 1 record, take that 9/1/2010 and 9/9/2010 into 2 datetime variables and with those compare....





    Saturday, September 11, 2010 2:07 PM
  • Can you try this query?

    SELECT
      *
    FROM
      dbo.mytable 
    WHERE
      CONVERT(datetime, Created, 121) >= '20100901'
      AND CONVERT(datetime,Finished, 121) <= '20100909';
    GO
    
    

    If you continue getting same error, then try this query:

    select * from dbo.mytable where isdate(created) = 0 or isdate(finished) - 0;

     


    AMB

    Some guidelines for posting questions...

    Saturday, September 11, 2010 4:59 PM
    Moderator
  •  

    its NOT returning single row with top records (top 1 * is NOT working) , giving the same error;

     

    --Let me explain my problem in details 

    1) I've one table UserAnswers it has column "AnswerValue" with datatype Varchar(255)

    2) Now, I've one view (userResults)  it's selecting from UserAnswers table, while selecting its converting Answervalue  column to datetime, like below based on condition 

     

      select CONVERT(datetime,Answervalue) as Created, CONVERT(datetime,Answervalue) as Finished

      from UserAnswers

     

    3) one more thing that, in userAnswers table the date are stored in varchar(255) format as follow, For example 

              2010-09-01 14:30:27.000 ,  2010-09-02 18:35:13.000,  2010-09-03 13:43:37.000 like this.

     

    now very simple step, select top 1 from VIEW 

    select top 1 *
    from userResults 
    where Created >= convert(datetime,'2010-09-01 11:14:54.000')
    

    the above query giving me the  "Conversion failed when converting date and/or time from character string" error

    Here the view has "Created" field in datetime, -- as while creating view from table we converted anservalue to datetime.

     

    The ISDATE =0 returning nothing, 

     select *
    from userResults 
    where isdate(Created) = 0 or isdate(Finished) = 0
    
    the above select query is not returning any thing, that means CREATED & FINISHED are in datetime,  I tried above query with 1 instead 0 and its returning all records. 

     

    BUT the above simple top 1 query is NOT working ??? I dont know whats going on ??

     

    Please advise me..

     


    pbrathod
    Monday, September 13, 2010 2:21 PM
  • Instead of checking the view for IsDate, try instead

    select * from UserAnswers where IsDate(AnswerValue) = 0

    This will show you bad records in the UserAnswers table.

     



    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 13, 2010 2:27 PM
    Moderator
  • what is the result of below queries??

    select top 1 *
    from (select GETDATE() as Created)t 
    where Created >= convert(datetime,'2010-09-01 11:14:54.000')
    
    select convert(datetime,'2010-09-01 11:14:54.000')
    
    
     select CAST( created as datetime) as Created , CAST( Finished as datetime) as Finished into #temp
    from userResults 
    where isdate(Created) = 1 and isdate(Finished) = 1
    
    select * from #temp where Created >= convert(datetime,'2010-09-01 11:14:54.000')
    
    
    • Marked as answer by Prashantr Monday, September 20, 2010 8:10 PM
    Monday, September 13, 2010 2:44 PM
  •  

     

     

    first thank you guys specifically ramireddy, finally got the expected result :)

     

    --NOT WORKING
    select * 
    from userResults 
    where CAST(created as datetime) >= convert(datetime,'9/1/2010')
    and CAST(Finished as datetime) <= convert(datetime,'9/9/2010')
    
    --WORKING
    select CAST(ur.created as datetime) as Created1 , CAST(ur.Finished as datetime) as Finished1,ur.*
     into #tempdata
    from userResults ur
    
    
    select * from #tempdata
    where CAST(Created1 as datetime) >= convert(datetime,'9/1/2010')
    and CAST(Finished1 as datetime) <= convert(datetime,'9/9/2010')
    
    
    --WORKING
    select convert(datetime,ur.created) as Created1 , convert(datetime,ur.Finished) as Finished1,ur.*
     into #tempdata1
    from userResults ur
    
    
    select * from #tempdata1
    where Created1 >= '9/1/2010'
    and Finished1  <= '9/9/2010'
    

     

    the above 1st script not working - giving the same error

     

    the 2nd & 3rd SQL script working fine and I got expected result :) , notice i used the Created1 & Finished1 columns in #tempdata table 

     

    Question is : why first i have to load all the data into #temp table for date conversion and then read from #team table ??

     

     


    pbrathod
    Monday, September 13, 2010 8:49 PM
  • You can use derived table instead

    select * from (select convert(datetime, Created) as Created, convert(datetime, Finished) as Finished from UserResults) u

    where Created >= '20100901' and Finished <='20100909'


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 13, 2010 9:00 PM
    Moderator
  •  

    the above derived table script is NOT working... giving the same error??


    pbrathod
    Monday, September 13, 2010 10:03 PM
  • It doesn't make much sense. I see no difference between temp table and this way. Of course, SET dateformat may be different and some other settings may differ, but still doesn't make sense.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, September 13, 2010 10:14 PM
    Moderator
  • Your date and time values are currently stored in the created and finished attributes as character strings using the ODBC canonical representation (with milliseconds) 'yyyy-mm-dd hh:mi:ss.mmm'. Unfortunately, even though this form is in accord with standard SQL, for historical reasons, in SQL Server it is language dependent. This means that depending on the connecting login's language setting, it can be interpreted differently, due to different dateformat interpretations by each language. Take a string such as '2010-02-13 00:00:00.000' as an example. Under us_english, the 02-13 part is interpreted as month-day (due to dateformat mdy), and hence the conversion works successfully. Try it:

    set language us_english;
    select cast('2010-02-13 00:00:00.000' as datetime);

    But under, say, British, it is interpreted as day-month (due to dateformat dmy), and hence the following code fails:

    set language British;
    select cast('2010-02-13 00:00:00.000' as datetime);

    In the long run, the way to deal with this is to ensure that you store your character strings in language-neutral form, e.g., 'yyyymmdd hh:mi:ss.mmm' - note there are no dashes between the date components. Or better yet, as datetime. In the short run, use the convert function and explicitly indicate the number of the style used in the character strings (documented in BOL). As mentioned, the style you're currently using is the ODBC canonical representation (with milliseconds). SQL Server uses the number 121 to represent this style. Therefore, your code should look something like:

    select *

    from ( select

             convert(datetime, created, 121) as Created1,

             convert(datetime, finished, 121) as Finished1,

             *

           from userResults ) as d

    where Created1  >= '20100901'

      and Finished1 <= '20100909';

     

    Several important points to notice:

    1. When converting the attributes created and finished to datetime, the code uses the convert function explicitly and indicates style 121, meaning that the conversion is not dependent on the user's language but rather on the meaning of style 121 (ODBC canonical with milliseconds).

    2. Once converted to datetime in the derived table, there's no need to convert again those attributes to datetime in the outer query.

    3. When specifying literals, I use the language neutral form 'yyyymmdd' and with this form you can safely rely on implicit conversion in this language neutral form. 

    Cheers

     


    -- BG
    • Proposed as answer by Naomi NModerator Tuesday, September 14, 2010 3:11 AM
    • Marked as answer by Ai-hua Qiu Friday, September 17, 2010 7:00 AM
    Tuesday, September 14, 2010 2:18 AM