Setting and resetting the DATEFORMAT in SQLServer 2005

Question

Is there a way to query the current DATEFORMAT 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 data to be inserted culture independent I store datetime-values represented in the invariant culture (month/day/year...) . The database server may run under different 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?

Answer 1

The following statement will perform a date parsing operation using a given date format (the @dateFormat variable) and then re enable the original date format.

declare @dateFormat nvarchar(3);
set @dateFormat = 'dmy';

declare @originalDateFormat nvarchar(3);
select @originalDateFormat = date_format from sys.dm_exec_sessions where session_id = @@spid;

set dateformat @dateFormat;

--Returns 1.
select isdate('31/12/2010');

set dateformat @originalDateFormat;

Note that the date format changes apply only to the current connection. Re enabling the original date format is only performed to ensure that later statements executed on the same connection are not affected by this change.

Answer 2

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.