none
how to build the datetime query when change datetime format of locale?

    问题

  • Hi,
    i have application in my user system. it has sqlqueries, and these are inbuilt to execute in another database server machine.
    My problem is when i am changing the DataTime format ie, English(india)(dd-mm-yyyy) to English(United staes)(mm-dd- yyyy) in my usermachine
    the queries are not executed correctly because the database machine is different datetime format.

    Ex:

    Usermachine-English(United state) --> mm-dd-yyyy
    Database servermachine-English(india)-->dd-mm-yyyy

    my query from User machine..

    UPDATE SiteMonthlyHitsTable SET [Last Accessed Date]='05-23-2012' WHERE [Page URL]='_vti_bin/owssvr.dll'

    this query mismatch with the database server datetime. so it has not executed. Pls help me how to built this qury to execute when
    support all the datetimeformat change.....

    Jack


    Nothing is impossible...

    2012年3月12日 5:39

答案

  • Hi Jack,

    '05-23-2012' is a string not datetime. In SQL Server, a datetime value is stored in an internal format, neither US nor UK.

    If you pass datetime values as strings, SQL Server needs to interpret and convert them. However, different regions have different datetime formats, so sometimes a conversion error occurs. For more information, please see: http://www.karaszi.com/SQLServer/info_datetime.asp 

    It seems that your application converts datetime values into strings and embedded them into the T-SQL queries. If your application access SQL Server via ADO.NET, I would suggest you use parameterized queries instead of string concatenation. 

    Please see: http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.

    2012年3月13日 6:02

全部回复