登入
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票
    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

    Chi
    2008年4月7日 上午 08:03
    |

解答

所有回覆

  • Question
    登入以投票
    0
    登入以投票

    Hello Chi,

     

    Please try the following method.

    SET DATEFORMAT DMY

    GO

     

    http://technet.microsoft.com/en-us/library/ms189491.aspx

    [SET DATEFORMAT (Transact-SQL)]

     

    Hope this helps.

    Best regards,

    Wen Yuan

    2008年4月7日 下午 12:23
    |
  • Question
    登入以投票
    0
    登入以投票
    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

    Chi
    2008年4月7日 下午 08:51
    |
  • Question
    登入以投票
    0
    登入以投票

    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
    |
  • Question
    登入以投票
    0
    登入以投票
    Hi Wen Yuan

    Thanks very much for your information.

    Thanks

    Chi
    --------------------------------
    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
    SET DATEFORMAT dmy
    GO

    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

    Chi

    2008年4月8日 下午 12:56
    |
  • Question
    登入以投票
    0
    登入以投票

    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
    |
  • Question
    登入以投票
    0
    登入以投票
    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
    |