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?

Answers


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.


Need Your Help

iPhone detect portrait lock setting

iphone ios4

I want to detect whether the user of my app as the portrait view lock set or not. How can I figuere this out?

Javascript alert show database field - asp

javascript variables asp-classic database-connection

I have a javascript alert which is displaying on form submit if a condition occurs. This is fine and I can use the following: