• Michael Valentine Jones (1/27/2011)


    william-700725 (1/27/2011)


    [font="Courier New"]About time somebody pointed out the convenience of the standardized formats. I've been using UCASE( CONVERT( char ( 11 ) , GETDATE() , 106 ) ) to create mil-spec dates for longer than I care to remember.

    Would it be excessively pedantic to note that format 120 (ODBC canonical [yyyy-mm-dd hh:mi:ss]) is another handy date string, especially when the output is going somewhere where some high-drag, low-speed user will want to sort the dates alphabetically (don't ask why)?[/font]

    I prefer to use format 121 to get the full precision of the DATETIME and DATETIME2 datatypes in a sortable character string.

    Example:

    select

    convert(varchar(23),getdate(),121)as [Datetime],

    convert(varchar(27),sysdatetime(),121)as [Datetime2]

    Results:

    Datetime Datetime2

    ----------------------- ---------------------------

    2011-01-27 12:58:07.567 2011-01-27 12:58:07.5678911

    [font="Courier New"]That's a good idea when the added precision is significant and accurate. On the other hand, when data is getting entered on a daily basis when things work right, the added precision is too easily confused with added accuracy.

    In my own experience, CONVERT( char ( 16 ) , GETDATE() , 120 ) is usually more than precise enough without adding extra volume to the the output -- in fact, more often than not I'll trim it down to char( 10 ).

    Lest you think I'm maligning my users' average attention to detail, I will state for the record that I'm reasonably sure that at least 3 out of any given 4 can breath with their mouth closed.[/font]