DateTime Trick

  • VOLKAN KUYUCUOGLU-450865

    Mr or Mrs. 500

    Points: 573

    Comments posted to this topic are about the item DateTime Trick

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Good question and interesting result. I'm surprised that it accepted mixed dots and dashes as separators in the date. I thought it would break if I changed it from 114 to 112. Thanks for this one!

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • john.arnott

    SSChampion

    Points: 11882

    Bob Hovious 24601 (10/25/2009)


    Good question and interesting result. I'm surprised that it accepted mixed dots and dashes as separators in the date. I thought it would break if I changed it from 114 to 112. Thanks for this one!

    I agree -- interesting result. The real trick here was not mentioned in the explanation. The mixed delimiters would indeed break the convert to varchar if we were relying on an implicit conversion from string to datetime, but are handled OK by the explicit Convert(datetime,'2009/01.01').

  • honza.mf

    SSCertifiable

    Points: 5519

    First time I consulted BOL before answering. Who remembers all those datetime styles?



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    honza.mf (10/26/2009)


    First time I consulted BOL before answering. Who remembers all those datetime styles?

    I'm glad you admitted this too. I always feel a bit guilty :blush: when I sneak a peek at BOL before answering, but probably justified in this case.

  • honza.mf

    SSCertifiable

    Points: 5519

    Andrew Watson-478275 (10/26/2009)


    honza.mf (10/26/2009)


    First time I consulted BOL before answering. Who remembers all those datetime styles?

    I'm glad you admitted this too. I always feel a bit guilty :blush: when I sneak a peek at BOL before answering, but probably justified in this case.

    I think QOD are a kind of a game. One can find an answer in BOL or even copy-paste the script to query analyzer, but it doesn't give me the good feeling. I prefer many bad answers.

    Today is an exception. I don't have phone book in my head and I don't want to have.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Rachel Byford

    Hall of Fame

    Points: 3437

    The BOL page with the meanings of the formats is the only one I keep in my favourites.

  • SanjayAttray

    SSChampion

    Points: 13157

    Does any one uses mixed datetime styles in a single query like the above one ?

    I haven't seen any.

    SQL DBA.

  • Tom Garth

    SSCertifiable

    Points: 6173

    SanjayAttray (10/26/2009)


    Does any one uses mixed datetime styles in a single query like the above one ?

    I haven't seen any.

    Probably not, but some readers no doubt will benefit from knowing the CONVERT function exists and what it can do. It's a good tool to use in reporting or on inquiry forms. I get a lot of use from:

    SELECT CONVERT(varchar(10),GETDATE(),126).

    More often with a Datetime column than with GETDATE(). Note the varchar length to drop the time element.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Toreador

    SSChampion

    Points: 11262

    Surely date formatting should normally be part of the presentation layer? I always return the whole datetime value and format it on the client, using their regional settings etc.

  • Kari Suresh

    Hall of Fame

    Points: 3712

    Interesting..:w00t:

    KSB
    -----------------------------
    Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

  • dmoldovan

    SSCertifiable

    Points: 6334

    It's useful to try to answer to such questions...:-)

    You may find out that

    SELECT CONVERT(DATETIME,'2009-01.01') and

    SELECT CONVERT(DATETIME,'2009-01-01')

    both work...:-)

  • The Dixie Flatline

    SSC Guru

    Points: 53253

    Toreador (10/27/2009)


    Surely date formatting should normally be part of the presentation layer? I always return the whole datetime value and format it on the client, using their regional settings etc.

    Good point, but CONVERT still comes in handy for bringing in data from an outside system, exporting a batch of data to an older system that uses YYYYMMDD, etc. It's also useful for converting strings to dates when the strings are in a format that T-SQL won't implicitly convert.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Tom Garth

    SSCertifiable

    Points: 6173

    Toreador (10/27/2009)


    Surely date formatting should normally be part of the presentation layer? I always return the whole datetime value and format it on the client, using their regional settings etc.

    I don't know that to be true. I've never heard that statement before. If I'm writing the code for the dataset as well as formatting the presentation layer. Then why shouldn't I use the fastest route for getting the results. Conversions, trimming, and even column concatenation are all methods that are more easily implementd with T-SQL than with some reporting applications.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • dmoldovan

    SSCertifiable

    Points: 6334

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

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