Post reply

Datetime conversion

  • May 22, 2008 at 9:06 pm

    #190978

    Comments posted to this topic are about the item Datetime conversion

  • May 23, 2008 at 9:17 am

    #819762

    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.000Apr 11 2008 5:10PM

    Regards,Yelena Varsha

  • May 23, 2008 at 9:40 am

    #819784

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

  • May 23, 2008 at 2:32 pm

    #819917

    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

  • May 24, 2008 at 7:51 am

    #819993

    Nice question......

  • May 26, 2008 at 6:55 am

    #820181

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

  • May 28, 2008 at 11:51 am

    #821365

    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

  • May 28, 2008 at 11:53 am

    #821366

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

  • December 4, 2012 at 12:27 am

    #1565139

    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 8 (of 8 total)

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