char to datetime!

  • Comments posted to this topic are about the item char to datetime!

  • Good question. Though it's simple, was not sure if it's implicit or explicit.

  • This actually answers a question that has been bugging me for a while. Where I work now they always use CAST/CONVERT on dates going to/from Char, but at my previous job they never bothered with it and it all seemed to work fine.

    Thank you - a simple question, but I've learned something.

  • Actually, you could do an implicit or explicit conversion....

    'implicit'

    SET @DateTimeVariable = @CharVariableContainingDateString

    vs.

    'explicit'

    SET @DateTimeVariable = convert(datetime,@CharVariableContainingDateString)

  • Mark Horninger (8/19/2009)


    Actually, you could do an implicit or explicit conversion....

    I agree. That was my first thought as well.

  • Yes, but my understanding (recently!) is that if you use CAST/CONVERT or any other functions on a date column that is indexed, the index is not utilised, making for a slower query.

    There's a lot of code here that does that and I believe it isn't needed.

  • If you rely on implicit conversion, is it going to convert the CHAR to DATETIME or DATETIME to CHAR? You are never really sure so you explicitly convert the side of the equation that has the least impact.

  • Is there a way to specify a constant date/time that isn't

    " SET @date = '2009-08-19' " ?

    (Or "CONVERT" from the date encoding of your choice, I suppose.

    Or "SET @date = 'Aug 19, 09' is fine.)

    DATEADD(day, (19-1), DATEADD(month, (8-1), DATEADD(year, (2009-1900), 0)))

    gives the same result, but you wouldn't, would you?

  • Cliff Jones (8/19/2009)


    If you rely on implicit conversion, is it going to convert the CHAR to DATETIME or DATETIME to CHAR? You are never really sure so you explicitly convert the side of the equation that has the least impact.

    You're not sure if you haven't memorised the precedence order of data types. 🙂

    If you suspect colleagues haven't memorised them, you do explicit CAST or CONVERT (I think CAST is ANSI SQL)...

    In an expression, including an equality or inequality test, one data type is elevated to match the other.

    So IF ( 'abc' '2009-04-15') " for ages. But there isn't one char expression of a date, anyway (I just said). The one you're mainly liable to try that won't work is such as

    'A' + 1, where presumably you want 'A1'.

    There I use STR(), sometimes REPLACE(STR(...), ' ', '0').

    In an assignment (SET x = y), of course conversion is from the type of expression y to the type of column/variable/whatever, x.

  • Implicit type conversions have been shown to work differently from one SQL Server version to another. We had a question recently that exposed this behavior. We witnessed it as well when we ported our application from SQL 2000 to SQL 2005. I don't recall the exact circumstances so this is anecdotal.

  • Cliff Jones (8/19/2009)


    Mark Horninger (8/19/2009)


    Actually, you could do an implicit or explicit conversion....

    I agree. That was my first thought as well.

    Me too. Since there was no 'either implicit or explicit' option, I got it right by a 50% guess 🙂

  • The question doesn't actually ask what the author thinks.

    I got it right, but only by trying to outthink the author.

    None of the answers given were actually sequitur to the question.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (8/19/2009)


    The question doesn't actually ask what the author thinks.

    I got it right, but only by trying to outthink the author.

    None of the answers given were actually sequitur to the question.

    Exactly what I thought when I was staring at the answers, frustrated that I had to take a wild stab in the dark before being able to post... Unfortunately I wasn't successful in my attempt to outthink the author.

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • I agree with the last two posts. I got it wrong cause I can't mind read.

    I can think of many examples where the cast is not implicit...

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Christopher Stobbs (8/24/2009)


    I agree with the last two posts. I got it wrong cause I can't mind read.

    I can think of many examples where the cast is not implicit...

    Well, I disagree with those two posts and with you. Is the conversion from tinyint to int implicit, explicit, or forbidden? It's implicit. But as far as I can see you are telling me that because it is perfectly valid to write

    declare @integer int

    declare @octet tinyint

    select @octet = 0

    select @integer = cast (@octet as int)

    the conversion from tinyint to int is explicit. Well, I think that's a very strange point of view.

    The descriptions in BOL seem to me quite clear. In fact there's even a nice matrix showing which conversions are implicit, which explicit, and which forbidden. It's been there since SQL 2000. It's still there in SQL 2008 R2.

    Tom

Viewing 15 posts - 1 through 15 (of 16 total)

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