Datetime conversion

  • Kev Riley

    SSCrazy Eights

    Points: 9026

    Comments posted to this topic are about the item Datetime conversion

  • Yelena Varshal

    SSC-Dedicated

    Points: 34251

    What is interesting, that if you don't convert to the character string, the format is different:

    set language us_english

    set dateformat dmy

    go

    declare @date datetime

    set @date = '11 apr 2008 17:10'

    select left(@date,1)

    select @date, convert(varchar(20), @date)

    returns:

    A

    2008-04-11 17:10:00.000 Apr 11 2008 5:10PM

    Regards,Yelena Varsha

  • Kev Riley

    SSCrazy Eights

    Points: 9026

    Yes - that shows the difference between the default conversion format and the default display format. I've seen many people get caught out by that before, expecting that left(@date,1) = '2'....hmm maybe I should have used that as one of the wrong answers....

  • Tom Garth

    SSCertifiable

    Points: 6173

    Yes. 2 should have been an option. I would have gotten it wrong then instead of testing it to get it right.

    Nice QoD. We are destined to constantly forget the simplest of things.

    And...

    select @date, convert(varchar(20), @date)

    is the same as

    select @date, convert(varchar(20), @date, 0)

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

    SSC-Insane

    Points: 24681

    Nice question......

  • Terry Maher

    SSC Enthusiast

    Points: 157

    This is probably a stupid question, but what controls the formatting of a datetime column with no cast/convert (in SQL query analyzer or management studio)?

  • Yelena Varshal

    SSC-Dedicated

    Points: 34251

    Either Microsoft Programmers or Defaults.

    In the CAST and CONVERT (TSQL) BOL article there is a table with formats. My date is returned as the following if Management Studio query window withput formatting:

    2000-04-06 18:06:31.000

    according to the table, the format name and description for that are:

    ODBC canonical (with milliseconds)

    yyyy-mm-dd hh:mi:ss.mmm(24h)

    It is not just default:

    Default mon dd yyyy hh:miAM (or PM)

    My assumption that it may be a front end (Management Studio) programmers formatted it this way because of 2 things:

    1. When I use Profiler to profile running a query in Management Studio, the Profiler shows the original query, so the formatting is done in the front end.

    2. Linked Server to Oracle query returnes dates /times in the same format as when running a query towards SQL Server.

    Regards,Yelena Varsha

  • Michael Spencer

    SSC-Addicted

    Points: 414

    Best QoD in a while. More like this, please!

  • kapil_kk

    SSC-Insane

    Points: 21316

    good question but confusing one....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 9 posts - 1 through 9 (of 9 total)

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