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?

Solution 1

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.

Solution 2

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.

Solution 3

What about the date_ format column in sys.dm_ exec_sessions?

You can always view your own session so no server level rights would be needed

Solution 4

If it's not too late submit your dates as YYYYMMDD to SQL Server and they will always be inserted correctly regardless of DMY or MDY dateformat settings

SET DATEFORMAT is also not permanent. In my experience it affects that particular connection. Some people say session only but I found if the connection is returned to the pool and reused before it is lost then it retains the dateformat setting set by a previous user.

Solution 5

I found a way to get this in a table of user options elsewhere, and converted it to get just the dateformat. It may be useful to others (note their are various other settings in the same table so you could check if ansi_nulls are set and various other things the same way):

create table #temp (SetOption nvarchar(50),Val nvarchar(50))

insert into #temp
exec sp_executesql N'dbcc useroptions'

select val from #temp where setoption='dateformat'

drop table #temp

View more solutions