Setting and resetting the DATEFORMAT in SQLServer 2005
Is there a way to query the current DATEFOMRAT SQLServer 2005 is currently using with T-SQL?
I have an application that reads pregenerated INSERT-statements and executes them against a database. To make the the data to be inserted culture independent I store datetime-values represented in the invariant culture (month/day/year...) . The database server may run under diffenrent locales, depending on the system locale (maybe using day/month/year) , so the insert may crash because the datetime cannot be parsed.
I know there are the "SET LANGUAGE" and "SET DATEFORMAT" statements in T-SQL to set the locale to be used. I do not want to make these changes permanent (are they permanent?), so I'm looking for a way to read the DATEFORMAT from the DB, store it, change the format to my liking and reset it to the stored value after the data has been inserted.
Any ideas where to find that value in the server?
Set Language and Set DateFormat only affect the current session. If you Disconnect from the database and connect again, the language and Dateformat are set back to their default values.
There is a 'trick' you could use to determine if the DateFormat is m/d/y, or d/m/y.
Select DatePart(Month, '1/2/2000')
This date is valid either way. It's Jan 2, or Feb 1. If this query returns a 1, then you have MDY. If it returns a 2, you have DMY.