Changing Date format on SQL Server Management Studio /SQL Server RRS feed

  • 問題

  • Hi

    I am wondering if there any way I can chnage the Date format from US TO UK

    E.g. On the query if you want to select a record which the date field has value of 26th March 2008.

    If US format
    We need to do

    SELECT *
    FROM TableName
    WHERE DateField='03/26/2008'

    On the other hand if you enter the DateField in UK Format
    you will receive an Error

    [The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.]

    I would be grateful if you could let me know it is some setting on the SQL server to read the text value as US date format of UK date format. Or the changes I need to make is on SQL Management Studio or On my Computer ...etc

    Many thanks

    2008年4月7日 上午 08:03



  • Hello Chi,


    Please try the following method.




    [SET DATEFORMAT (Transact-SQL)]


    Hope this helps.

    Best regards,

    Wen Yuan

    2008年4月7日 下午 12:23
  • Hi Wen Yuan

    Thanks for the information, It work perfectly. But I need to run that command everytime if I want to use the format I prefer. Is there anyway I could set it as default, so I do not need to type it everytime?

    Many thanks

    2008年4月7日 下午 08:51
  • Hello Chi,


    I’m sorry, but this value only persists in connection session. Thereby, you have to set it every time. You may consider creating a Stored Procedure.


    Hope this helps,

    Wen Yuan

    2008年4月8日 下午 12:03
  • Hi Wen Yuan

    Thanks very much for your information.


    Sorry Wen Yuan

    The problem has not been fixed, When I used MS SQL Management Studio Create/ Alter the Stored procedure/Function, it does not keep that

    in that stored procedure.
    After I updated the function, and I click on Modify function again the command above disappeared.
    I am wondering it that command would work on Function.

    Do you have any suggest about any changes I need to make to make it work?

    Many thanks


    2008年4月8日 下午 12:56
  • Right click the sp in ssms and choose modify, it'll generate alter sp script. Add set commnd in the script and apply it.

    2008年4月9日 下午 03:47
  • Hey Rmiao

    Thanks for the information. It works now, I placed the Set Command at the very top of the SP, that's why it does not work before. Now I placed it after "AS", It works now : )

    Thanks very much for your help.

    2008年4月10日 上午 07:51