char to datetime!

  • VM-723206

    SSCrazy

    Points: 2964

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

  • Joy Smith San

    SSC-Insane

    Points: 24877

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

  • BrainDonor

    SSCoach

    Points: 19214

    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.

    Steve Hall
    Linkedin
    Blog Site

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

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

    'implicit'

    SET @DateTimeVariable = @CharVariableContainingDateString

    vs.

    'explicit'

    SET @DateTimeVariable = convert(datetime,@CharVariableContainingDateString)

  • Cliff Jones

    SSChampion

    Points: 10517

    Mark Horninger (8/19/2009)


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

    I agree. That was my first thought as well.

  • BrainDonor

    SSCoach

    Points: 19214

    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.

    Steve Hall
    Linkedin
    Blog Site

  • Cliff Jones

    SSChampion

    Points: 10517

    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.

  • rja.carnegie

    SSC Eights!

    Points: 923

    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?

  • rja.carnegie

    SSC Eights!

    Points: 923

    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.

  • Cliff Jones

    SSChampion

    Points: 10517

    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.

  • Toreador

    SSChampion

    Points: 11248

    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 🙂

  • GSquared

    SSC Guru

    Points: 260824

    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

  • Rob Goddard

    SSCrazy

    Points: 2572

    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]

  • Christopher Stobbs

    SSC-Insane

    Points: 21098

    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
  • TomThomson

    SSC Guru

    Points: 104772

    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 17 total)

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