How to Change the default date format in sqlserver 2005

  • Ramkumar.K

    Default port

    Points: 1405

    Hi All

    How can I change the date format in my sql server 2005?

    i.e

    select getdate()

    it gives 2007-09-03 18:27:06.463

    But I want to change it to say 03-09-2007.

    I need to change the format into dmy..

    Ramkumar . K
    Senior Developer
    ######################
    No Surrender... No Give Up....
    ######################

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88053

    Dates are not stored in a format - this is controlled when you query the database and can be set using DATEFORMAT. The other option is to use CONVERT and specify the format in the convert command.

    You can find more information on both in BOL (Book Online).

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Ramkumar.K

    Default port

    Points: 1405

    Hi williams..

    I agree with u..

    But whats my friend's requirement is to change the settings which attained by sql server 2005..

    by default it is coming like

    SET DATEFORMAT mdy;

    select getdate()

    Result:

    2008-07-28 10:26:25.920

    Can I able to change it in to Date/month/Year format

    Ramkumar . K
    Senior Developer
    ######################
    No Surrender... No Give Up....
    ######################

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88053

    You can use

    SET DATEFORMAT dmy;

    Or, you can use CONVERT in the select statement. Date formats are usually better controlled in the client.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • ch.mohini

    SSC-Addicted

    Points: 497

    it is default comes directly from instalation

  • Arcturus

    Mr or Mrs. 500

    Points: 563

    To set the date format according to your need you need to execute the following SP on master database:

    EXEC sp_configure 'default language', '23';

    '23' stands for the Code of the format details of which you can get by running

    select * from syslanguages

    After execution of this stored proc it will ask you to Run the RECONFIGURE statement to install.

    then Run

    RECONFIGURE;

    let me know if it works for you

  • ganesh.trg

    SSC Journeyman

    Points: 89

    Great reply... thanks for you wonderful answer.. I got the same problem.. Because of you i got released from that worry.. thank you so much saket 🙂

  • Ganesh Lohani

    Hall of Fame

    Points: 3091

    Try this:

    SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS MyDate

    I hope it will be helpful for you.

  • Ron Kunce

    SSCrazy

    Points: 2128

    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?

    Ron K.

    "Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler

  • Jeff Moden

    SSC Guru

    Points: 994289

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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