Datetime conversion

  • Comments posted to this topic are about the item Datetime conversion

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

    set language us_english

    set dateformat dmy


    declare @date datetime

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

    select left(@date,1)

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



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

    Regards,Yelena Varsha

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

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


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

  • 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)?

  • 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

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

  • good question but confusing one....

    To get quick answer follow this link:

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

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