Getting Date Format from SQL Server programatically

Question

Hi everyone! I just wonder if there is a way of getting programatically using C# the date format that a SQL Server is using for INSERTS and UPDATES. Thanks a Lot! Alejandro.

gjghkhj · Answer

I pass dates into stored procedures are DateTime values and SQL server works with them without a problem. If you are dynamically creating your SQL statements you may want to create a little method that you can reuse which takes a DateTime in as a parameter and returns a string that SQL server will like such as yyyy-mm-dd. Creating the string should be easy just use something like myString = myDate.Year + - + myDate.Month + - + myDate.Day. You might also have to add single quotes around the date in the SQL statement.

miker · Answer

From SQL Server Books Online: When specifying dates in comparisons or for input to INSERT or UPDATE statements use constants that are interpreted the same for all language settings: -ADO OLE DB and ODBC applications should use the ODBC timestamp date and time escape clauses of: { ts 'yyyy-mm-dd hh:mm:ss[.fff] '} such as: { ts '1998-09-24 10:02:20' } { d 'yyyy-mm-dd'} such as: { d '1998-09-24' } { t 'hh:mm:ss'} such as: { t '10:02:20'} -Applications using other APIs or Transact-SQL scripts stored procedures and triggers should use the unseparated numeric strings (for example yyyymmdd as 19980924). -Applications using other APIs or Transact-SQL scripts stored procedures and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the date and smalldate data types and character string data types. For example this statement is interpreted the same for all language or date format connection settings: SELECT * FROM Northwind.dbo.Orders WHERE OrderDate = CONVERT(DATETIME '7/19/1996' 101)