DateTime Trick

  • Comments posted to this topic are about the item DateTime Trick

  • 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

  • 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').

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



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

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

  • 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

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

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

    I haven't seen any.

    SQL DBA.

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

  • 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

  • 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...:-)

  • 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

  • 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

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

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