none
conversion failed when converting datetime from character string

Answers

  • Haris,

    It may be date data failure (invalid date data) or you have to use a style number in the conversion. See demo script below.  Let us know if helpful.

     -- String to datetime conversion successful  
    SELECT DatetimeFormat = CAST ('2015-01-01' AS datetime)  
    -- 2015-01-01 00:00:00.000  
     
    -- String to datetime conversion fails without style number  
    SELECT DatetimeFormat = CONVERT (datetime, '31.01.2015')  
    /* 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.  
    */  
    -- String to datetime conversion succeeds with style number 104  
    -- dd.mm.yyyy conversion  
    SELECT DatetimeFormat = CONVERT (datetime, '31.01.2015', 104)  
    -- 2015-01-31 00:00:00.000  
     
    -- String to datetime conversion fails  
    SELECT DatetimeFormat = CAST ('2015-13-01' AS 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.  
    */  
     
    -- String to datetime conversion fails  
    SELECT DatetimeFormat = CAST ('2015-AA-01' AS datetime)  
    /*  
    Msg 241, Level 16, State 1, Line 1  
    Conversion failed when converting date and/or time from character string.  
    */  
     
    -- Check data for validity with the ISDATE function  
    SELECT ISDATE ('2015-13-01')  
    -- 0 - invalid date data 

    Datetime conversion article: http://www.sqlusa.com/bestpractices/datetimeconversion/

     


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

    Sunday, February 01, 2009 11:07 AM

All replies

  • Ok. Is there a question to this statement? Probably little bit of information on exactly what you tried will help us identify or solve your issue at hand. You may want to read this first.

    How can I Prepare My Question to Increase the Possibility of Getting a Good Solution?


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Sunday, February 01, 2009 9:17 AM
  • Haris,

    It may be date data failure (invalid date data) or you have to use a style number in the conversion. See demo script below.  Let us know if helpful.

     -- String to datetime conversion successful  
    SELECT DatetimeFormat = CAST ('2015-01-01' AS datetime)  
    -- 2015-01-01 00:00:00.000  
     
    -- String to datetime conversion fails without style number  
    SELECT DatetimeFormat = CONVERT (datetime, '31.01.2015')  
    /* 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.  
    */  
    -- String to datetime conversion succeeds with style number 104  
    -- dd.mm.yyyy conversion  
    SELECT DatetimeFormat = CONVERT (datetime, '31.01.2015', 104)  
    -- 2015-01-31 00:00:00.000  
     
    -- String to datetime conversion fails  
    SELECT DatetimeFormat = CAST ('2015-13-01' AS 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.  
    */  
     
    -- String to datetime conversion fails  
    SELECT DatetimeFormat = CAST ('2015-AA-01' AS datetime)  
    /*  
    Msg 241, Level 16, State 1, Line 1  
    Conversion failed when converting date and/or time from character string.  
    */  
     
    -- Check data for validity with the ISDATE function  
    SELECT ISDATE ('2015-13-01')  
    -- 0 - invalid date data 

    Datetime conversion article: http://www.sqlusa.com/bestpractices/datetimeconversion/

     


    Kalman Toth, SQL Server 2008 Training, SSAS, SSIS, SSRS, BI: www.SQLUSA.com

    Sunday, February 01, 2009 11:07 AM
  • Also note that it is the user's language setting that will define how SQL Server converts a date string into datetime type.

    To get around this, you could use something like:

    SET LANGUAGE 'us_english'

    at the very top of your stored procedure or as the first(ish) line of your query.

    You would set 'us_english' of course only if that is the date format you are using/expecting (ie MM/dd/yyyy, which is 2 digit month, 2 digit day, and 4 digit year).

    You can use:

    exec SP_HELPLANGUAGE

    to get a list of languages and their codes.

    From experience the safest way to pass a string as a date to SQL server is in an ISO format such as 'yyyyMMdd' or 'yyyyMMdd hh:mm:ss.nnn', using the 24 hour format, of course.

    Some people even recomend using 'MMM dd, yyyy' (such as 'Feb 2, 1997') which certainly is unmistakable, but I'm wondering if that will work on a non-english version of SQL Server (gut feeling is "most likely" becasue the internal engine will still be us_english based, afaict).

    Personally, I'd like to see the input of a non-english SQL Server using DBA on this issue - google seems to bring up a lot of conflicting info.

    Here a little script to play around with - if you run it, you'll notice setting the language to Greek will case an error on the SP call of exec TEST_DATEFORMAT2 N'îλληνικά'; - which proably prooves that using the short English month names (MMM) can fail, answering my own question above:

    CREATE procedure [dbo].[TEST_DATEFORMAT]  
    @useLanguage as nvarchar(50)  
    as 
    begin 
    set language @useLanguage;  
    select   
    convert(nchar,convert(datetime,'20090112'),9) as [Iso]   
    convert(nchar,convert(datetime,'01/12/2009'),9) as [US];  
    end;  
    CREATE procedure [dbo].[TEST_DATEFORMAT2]  
    @useLanguage as nvarchar(50)  
    as 
    begin 
    set language @useLanguage;  
    select   
    convert(nchar,convert(datetime,'20090112'),9) as [Iso]   
    convert(nchar,convert(datetime,'01/12/2009'),9) as [US]  
    convert(nchar,convert(datetime,'Jan 12, 2009'),9) as [English Month];  
    end;  
     
    exec TEST_DATEFORMAT 'us_english';  
    exec TEST_DATEFORMAT 'British';  
    exec TEST_DATEFORMAT 'Deutsch';  
    exec TEST_DATEFORMAT 'Suomi';  
    exec TEST_DATEFORMAT N'ελληνικά';  
    --exec SP_HELPLANGUAGE /*shows languages possible */  
    exec TEST_DATEFORMAT2 'us_english';  
    exec TEST_DATEFORMAT2 'British';  
    exec TEST_DATEFORMAT2 'Deutsch';  
    exec TEST_DATEFORMAT2 'Suomi';  
    exec TEST_DATEFORMAT2 N'ελληνικά'-- this will error! 

    Cheers,
    Martin
    Sunday, February 01, 2009 9:30 PM
  • What is the string?

    Adam


    Environment + Scope + Time Constraints + Management + Experience = Approach
    Sunday, February 01, 2009 10:35 PM
  • Thanks....
    Monday, February 02, 2009 7:15 AM