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

  • Pergunta

  • 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
    segunda-feira, 7 de abril de 2008 08:03

Respostas

Todas as Respostas

  • 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

    segunda-feira, 7 de abril de 2008 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

    Chi
    segunda-feira, 7 de abril de 2008 20: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

    terça-feira, 8 de abril de 2008 12:03
  • 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

    terça-feira, 8 de abril de 2008 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.

    quarta-feira, 9 de abril de 2008 15: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.


    quinta-feira, 10 de abril de 2008 07:51