none
convert character data to datetime

    Question

  • Hi!  I need to insert character data into a SQL 2005 table in the datetime format.  The strings I need to convert are in the following format:  ddmmyyyy

    I tried using case and convert:

    cast('08162006' as datetime))
    convert(datetime,'08162006',101))

    Both attempts fail with the following error:

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

    Any advice is greatly appreciated.  Thanks.


    Wednesday, August 16, 2006 8:00 PM

Answers

All replies

  • 
    You'll have to get the slashes in there to get it conformant with style 101.  Try:
     

    select convert(datetime, stuff(stuff('08162006', 3, 0, '/'), 6, 0, '/') ,101)

     
    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     
    Hi! I need to insert character data into a SQL 2005 table in the datetime format. The strings I need to convert are in the following format: ddmmyyyy

    I tried using case and convert:

    cast('08162006' as datetime))
    convert(datetime,'08162006',101))

    Both attempts fail with the following error:

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

    Any advice is greatly appreciated. Thanks.


    Wednesday, August 16, 2006 8:12 PM
  • YES! YES! YES!  Thank you!  For those interested in using cast(), this works too:

    cast(stuff(stuff('08162006', 3, 0, '/'), 6, 0, '/') as datetime)

    Wednesday, August 16, 2006 8:25 PM
  • Well, yes and no. While the statement doesn't produce any errors, it doesn't do what you think either.

    There is no point at all formatting a date string in any way when it's to be converted to a datetime. It doesn't make any difference at all. The 'date' stored in a datetime datatype doesn't even look like a date, it's just numbers.

    So, while
    select cast(stuff(stuff('08162006', 3, 0, '/'), 6, 0, '/') as datetime)
    returns something, it doesn't do what you think.

    Here's what I get in QA, not quite expected I believe.
    ------------------------------------------------------
    2006-08-16 00:00:00.000

    Also, this example
    select convert(datetime,'08162006',101)

    Here, the style parameter isn't used. It's only used when you do datetime => char conversions, never on char => datetime, so it's not working like you expect here.

    All a convert/cast string => datetime needs is a string that is formatted, and contains a valid date, then the conversion will succeed. However, if it's a delimited string (eg '08/16/2006'), you're suceptible to language settings, and may produce an incorrect date or an error. (US and UK are examples)

    What you have from the beginning is almost waht you need, though mmddyyyy isn't a valid date from SQL Server's point of view. The easiest would imo be to just move the yyyy part from right to left, so you get yyyymmdd, then you can cast that to a datetime.

    Something along these lines:

    declare @d char(8)
    set @d = '08162006'
    select cast(right(@d, 4) + left(@d, 4) as datetime)

    -----------------------
    2006-08-16 00:00:00.000


    The output, however, is displayed according to the tool defaults and connection settings used when running the query.
    If you want to control display format of the datetime, then you have to convert the datetime back to a string again in order to benefit from the style parameters

    select convert(char(10), cast(right(@d, 4) + left(@d, 4) as datetime), 101)
    ----------
    08/16/2006

    =;o)
    /Kenneth

    Thursday, August 17, 2006 7:33 AM
  • 
    I'm not sure why you feel that the solution posted didn't work.  The string is clearly in MMDDYYYY format, and that's exactly how the conversion treated it.  Definitely the expected results.
     
    As for your contention that, "the style parameter isn't used. It's only used when you do datetime => char conversions, never on char => datetime," that is not correct.  Try the following:
     
    select convert(datetime, '02-01-2006', 105), convert(datetime, '02-01-2006', 110)
    The problem with dates that are incorrectly formatted (i.e., not consistent with the ISO standard) is that they're ambiguous.  In the above example, SQL Server has no way of knowing if you're talking about January 2 or February 1.  So you have to tell it -- and you do that via the style parameter.  The only time I wouldn't use the style parameter (in order to be on the safe side if the code has to switch locales) is when converting dates that are already properly formatted in YYYYMMDD or YYYY-MM-DD format (ISO style).
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     

    Well, yes and no. While the statement doesn't produce any errors, it doesn't do what you think either.

    There is no point at all formatting a date string in any way when it's to be converted to a datetime. It doesn't make any difference at all. The 'date' stored in a datetime datatype doesn't even look like a date, it's just numbers.

    So, while
    select cast(stuff(stuff('08162006', 3, 0, '/'), 6, 0, '/') as datetime)
    returns something, it doesn't do what you think.

    Here's what I get in QA, not quite expected I believe.
    ------------------------------------------------------
    2006-08-16 00:00:00.000

    Also, this example
    select convert(datetime,'08162006',101)

    Here, the style parameter isn't used. It's only used when you do datetime => char conversions, never on char => datetime, so it's not working like you expect here.

    All a convert/cast string => datetime needs is a string that is formatted, and contains a valid date, then the conversion will succeed. However, if it's a delimited string (eg '08/16/2006'), you're suceptible to language settings, and may produce an incorrect date or an error. (US and UK are examples)

    What you have from the beginning is almost waht you need, though mmddyyyy isn't a valid date from SQL Server's point of view. The easiest would imo be to just move the yyyy part from right to left, so you get yyyymmdd, then you can cast that to a datetime.

    Something along these lines:

    declare @d char(8)
    set @d = '08162006'
    select cast(right(@d, 4) + left(@d, 4) as datetime)

    -----------------------
    2006-08-16 00:00:00.000


    The output, however, is displayed according to the tool defaults and connection settings used when running the query.
    If you want to control display format of the datetime, then you have to convert the datetime back to a string again in order to benefit from the style parameters

    select convert(char(10), cast(right(@d, 4) + left(@d, 4) as datetime), 101)
    ----------
    08/16/2006

    =;o)
    /Kenneth

    Thursday, August 17, 2006 3:40 PM
  • Good discussion.  I'm having a new problem.  I want to use the following sproc to perform the conversion when I insert records:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    CREATE proc [dbo].[spInsertType11TransactionData]
    @LoadTransactionCode decimal,
    @CompanyID bigint,
    @Period    decimal,
    @CardType decimal,
    @StartDate datetime,
    @EndDate datetime,
    @PeriodCompleteIndicator decimal,
    @OptionalField1    char(26) = null,
    @OptionalField2    char(26) = null,
    @OptionalField3    char(26) = null,
    @OptionalField4    char(26) = null
    as

    insert into period values
        (    @LoadTransactionCode,
            @CompanyID,
            @Period,
            @CardType,
    --        convert(datetime, stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') ,101),
    --        convert(datetime, stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') ,101),
            cast(stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') as datetime),
            cast(stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') as datetime),
            @PeriodCompleteIndicator,
            @OptionalField1,
            @OptionalField2,
            @OptionalField3,
            @OptionalField4
        )

    I attempt to use either cast() or convert() in the sproc, but I get the following SQLException error:

    Error converting data type varchar to datetime.

    The varchar comes from my SQLParameters collection for the date column:

    pc.Add("StartDate", SqlDbType.VarChar, 0, "StartDate");

    Do I need to do this another way in the sproc?

    Thanks!
    Thursday, August 17, 2006 8:17 PM
  • 
    Can you run Profiler and see what value is actually getting passed into the stored procedure?
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     
    Good discussion. I'm having a new problem. I want to use the following sproc to perform the conversion when I insert records:

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    CREATE proc [dbo].[spInsertType11TransactionData]
    @LoadTransactionCode decimal,
    @CompanyID bigint,
    @Period decimal,
    @CardType decimal,
    @StartDate datetime,
    @EndDate datetime,
    @PeriodCompleteIndicator decimal,
    @OptionalField1 char(26) = null,
    @OptionalField2 char(26) = null,
    @OptionalField3 char(26) = null,
    @OptionalField4 char(26) = null
    as

    insert into period values
    ( @LoadTransactionCode,
    @CompanyID,
    @Period,
    @CardType,
    -- convert(datetime, stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') ,101),
    -- convert(datetime, stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') ,101),
    cast(stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') as datetime),
    cast(stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') as datetime),
    @PeriodCompleteIndicator,
    @OptionalField1,
    @OptionalField2,
    @OptionalField3,
    @OptionalField4
    )

    I attempt to use either cast() or convert() in the sproc, but I get the following SQLException error:

    Error converting data type varchar to datetime.

    The varchar comes from my SQLParameters collection for the date column:

    pc.Add("StartDate", SqlDbType.VarChar, 0, "StartDate");

    Do I need to do this another way in the sproc?

    Thanks!
    Thursday, August 17, 2006 8:36 PM
  • I think the problem is here:

    Parameter:

    @StartDate datetime,
    @EndDate datetime,

    Code:
            cast(stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') as datetime),
            cast(stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') as datetime),

     


    declare @StartDate datetime,
      @EndDate datetime
    set @startDate = '08162006'
    set @endDate = '08202006'

    select  cast(stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') as datetime),
            cast(stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') as datetime)

    Msg 242, Level 16, State 3, Line 4
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    Msg 242, Level 16, State 3, Line 5
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    You want to change the parms to string, I think:


    declare @StartDate char(8),
      @EndDate char(8)
    set @startDate = '08162006'
    set @endDate = '08202006'

    select  cast(stuff(stuff(@StartDate, 3, 0, '/'), 6, 0, '/') as datetime),
            cast(stuff(stuff(@EndDate, 3, 0, '/'), 6, 0, '/') as datetime)

    I know how hard this stuff is to notice in code youv'e written :)

    Thursday, August 17, 2006 9:38 PM
  • Mr. Davidson,

    Thanks much!  I thought I had to maintain the datatype in the parameter declaration of the sproc:

    @StartDate datetime,
    @EndDate datetime,

    to match the datatype in the table columns.  I guess it only matters at the point of actually inserting the data, hence the CAST().  Is this correct?

    Regards,

    Friday, August 18, 2006 12:53 PM
  • Cheers Adam.

    You're absolutely correct. =:o)
    Must confess that I've until now, completely missed the BOL text about style being used as an input parameter when converting to datetime.
    I've always only seen (and read even) it as a formatting method when producing a display from datetime, and as such, the thread example does not work, since the output doesn't necessarily display according to the style.

    My bad, though I learned someting new =;o)

    /Kenneth

     NNTP User wrote:
    

    I'm not sure why you feel that the solution posted didn't work.  The string is clearly in MMDDYYYY format, and that's exactly how the conversion treated it.  Definitely the expected results.
     
    As for your contention that, "the style parameter isn't used. It's only used when you do datetime => char conversions, never on char => datetime," that is not correct.  Try the following:
     
    select convert(datetime, '02-01-2006', 105), convert(datetime, '02-01-2006', 110)
    The problem with dates that are incorrectly formatted (i.e., not consistent with the ISO standard) is that they're ambiguous.  In the above example, SQL Server has no way of knowing if you're talking about January 2 or February 1.  So you have to tell it -- and you do that via the style parameter.  The only time I wouldn't use the style parameter (in order to be on the safe side if the code has to switch locales) is when converting dates that are already properly formatted in YYYYMMDD or YYYY-MM-DD format (ISO style).
     

    --
    Adam Machanic
    Pro SQL Server 2005, available now
    http://www..apress.com/book/bookDisplay.html?bID=457
    --
     
     -- snipped for brevity --
    Monday, August 21, 2006 8:14 AM