Default Date format

[keenanbr]

Is there a way to set the default date format.
I have to enter

set dateformat mdy

each time

 

[LogicMagic]

The user options setting of sp_configure allows server-wide settings and works across multiple databases. This setting also behaves like an explicit SET statement, except that it occurs at login time.


So... Try something like

USE master
EXEC sp_configure 'set dateformat mdy'
RECONFIGURE WITH OVERRIDE

http://www.logicmagic.com

 

[keenanbr]

Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'set dateformat mdy' does not exist, or it may be an advanced option.

Valid configuration options are:

 

[SQLBill]

If it's an advanced option you need to run it like this:

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTION', '1'
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'SET DATEFORMAT MDY'
GO
RECONFIGURE
GO

You can get a list of advanced options by running this;
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTION', '1'
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE

Refer to the BOL, use the Index tab and enter SP_CONFIGURE. Choose the SP_CONFIGURE Transact-SQL option. In there you will also see a link for Setting Configuration Options. Check out that also.

You might need to set the options to ALLOW UPDATES

EXEC SP_CONFIGURE 'ALLOW UPDATES', '1'
GO

Check this all out in the BOL as it makes changes to the system tables.

-SQLBill
BOL = Books OnLine = Microsoft SQL Server's Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[keenanbr]

I tried :

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTION', '1'
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'SET DATEFORMAT MDY'
GO
RECONFIGURE
GO

Got :

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Server: Msg 5808, Level 16, State 1, Line 1
Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.
Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'SET DATEFORMAT MDY' does not exist, or it may be an advanced option.

Valid configuration options are:

Server: Msg 5808, Level 16, State 1, Line 1
Ad hoc updates to system catalogs not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration.

Anyone got ant ideas

 

[SQLBill]

Run this:

EXEC SP_CONFIGURE 'SHOW ADVANCED OPTION', '1'
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE

That will give you a list of the advanced options. Maybe LogicMagic has it wrong and SET DATEFORMAT dmy is not an advanced option.

-SQLBill

 

[keenanbr]

The solution, for anyone interested, was to change the language setting in ODBC. Thanks to all for you replies.

 

[SQLBill]

Sometimes we overlook the obvious. Oh well.....

-SQLBill