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

Question

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...

Answer 1

Use the ANSI date format - YYYYMMDD for string date literals:

UPDATE SiteMonthlyHitsTable SET [Last Accessed Date]='20120523' WHERE [Page URL]='_vti_bin/owssvr.dll'

Also, always use style number in CONVERTs, don't rely on the default dmy or mdy setting:
http://www.sqlusa.com/bestpractices/datetimeconversion/

When designing a table, use DATETIME or DATE (SQL Server 2008) data types, don't use string dates.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

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.