Post reply

Varchar to Datetime Conversion

  • November 6, 2008 at 11:07 pm

    #214982

    i have been playing with date datatype for long but found this strange problem today. It could be i haven't noticed it before or this problem just popped up today.

    Lets say we have a table only with date data. Now if i validate the data with isDate(), i should be expecting SQL server to return me all valid dates. But it didn't happen. I am putting an example below for the problem i have faced today.

    create table #tmp

    (date1 varchar(10))

    insert into #tmp

    select '10.12.2006' union all

    select '10-12-2006' union all

    select '10/12/2006' union all

    select '10\12\2006' union all

    select '10 10 2006' union all

    select '10 10 06'

    select *, isdate(date1) as validDate

    from #tmp

    drop table #tmp

    Now if you look at second last and last row with date value as '10 10 2006' and '10 10 06', isDate() tells that it is not a valid date. How and why this happened?

    I am of the view point that this is valid date which i can convert to valid datetime format using 6 or 106 datetime conversion code. There is no example in BOL or nowhere mentioned that i can't convert this kind of varchar datatype to datetime datatype.

    Has anyone faced this kind of problem before? If yes, what was the solution to it.

  • November 6, 2008 at 11:10 pm

    #895314

    sorry forgot to gave more info. I am using SQL Server 2005 SP2.

    Cheers

  • November 6, 2008 at 11:22 pm

    #895317

    anam (11/6/2008)

    i have been playing with date datatype for long but found this strange problem today. It could be i haven't noticed it before or this problem just popped up today.

    Lets say we have a table only with date data. Now if i validate the data with isDate(), i should be expecting SQL server to return me all valid dates. But it didn't happen. I am putting an example below for the problem i have faced today.

    create table #tmp

    (date1 varchar(10))

    insert into #tmp

    select '10.12.2006' union all

    select '10-12-2006' union all

    select '10/12/2006' union all

    select '10\12\2006' union all

    select '10 10 2006' union all

    select '10 10 06'

    select *, isdate(date1) as validDate

    from #tmp

    drop table #tmp

    Now if you look at second last and last row with date value as '10 10 2006' and '10 10 06', isDate() tells that it is not a valid date. How and why this happened?

    I am of the view point that this is valid date which i can convert to valid datetime format using 6 or 106 datetime conversion code. There is no example in BOL or nowhere mentioned that i can't convert this kind of varchar datatype to datetime datatype.

    Has anyone faced this kind of problem before? If yes, what was the solution to it.

    use the separator between the dd mm and yy.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • November 7, 2008 at 12:52 am

    #895340

    http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

    Madhivanan

    Failing to plan is Planning to fail

  • November 7, 2008 at 1:09 am

    #895346

    can you try like: selectconvert (varchar, getdate(), 106), for more info see SQL BOL CAST & Convert Funtions.

  • November 7, 2008 at 1:47 am

    #895357

    DECLARE@Sample TABLE

    (

    date1 varchar(10)

    )

    INSERT@Sample

    SELECT'10.12.2006' UNION ALL

    SELECT'10-12-2006' UNION ALL

    SELECT'10/12/2006' UNION ALL

    SELECT'10\12\2006' UNION ALL

    SELECT'10 10 2006' UNION ALL

    SELECT'10 10 06'

    SELECTtheDate,

    ISDATE(theDate)

    FROM(

    SELECTSUBSTRING(date1, 7, 4) + '-' + SUBSTRING(date1, 4, 2) + '-' + SUBSTRING(date1, 1, 2) AS theDate

    FROM@Sample

    ) AS d

    N 56°04'39.16"
    E 12°55'05.25"

  • November 7, 2008 at 3:08 pm

    #895845

    Hi All

    Thanks for your reply and solutions. Actually i was a big fool before posting this question onto forum, I should have double checked my problem. May be it was friday. 😉

    '10 10 2006' or '10 10 06' is not a valid date. it corresponds to format of 'dd mm yy'. There is no such format defined in BOL. The actual format defined in BOL for 06 or 106 codes in convert functions is 'dd mon yy' instead of 'dd mm yy'. That was the catch i should have picked up in first instance.

    i think one learn from mistakes 🙂

  • November 8, 2008 at 12:00 pm

    #895982

    The purpose of ISDATE is to tell you ahead of time whether or not an attempt to CAST or CONVERT something into a Datetime format is going to suceed or fail. If you think that ISDATE is wrong, the way to test it is to try to CAST or CONVERT the same value.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply