Home Forums SQL Server 7,2000 Replication How to Change the default date format in sqlserver 2005 RE: How to Change the default date format in sqlserver 2005

  • October 30, 2013 at 3:42 pm

    #1662865

    Ron Kunce (10/30/2013)

    Along this line about the Default datetime value display (in SQL Server 2005) I do not understand why if I declare a variable as a datetime type and set it to the value returned from the GetDate() function I get back a format the same as the above (i.e., 2013-10-30 14:29:40.710), however when returning a datetime type from a user defined function call it is displayed as "Oct 10 2013 2:29PM" since there are no conversions or SET DATEFORMAT involved why is this second format automatically chosen for display?

    My guess would be that it's because different people wrote different sections of code in SQL Server. There are two bottom lines here, though.

    1st and unless there is no front-end involved, you shouldn't worry about formatting dates in SQL Server. Let the front-end take care of that so that regional settings can kick in.

    2nd, if you absolutely must format dates in SQL Server for display or, perhaps, output to a file, then you should absolutely and explicitly control the date format using CONVERT. Trusting default date formats is kind of like trusting the "natural sort order" of a query... when you least expect it, it will change.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)