Changing the default date format

Question

Post reply

Changing the default date format

mrivera

Ten Centuries

Points: 1050
More actions
November 4, 2006 at 11:58 am

#75087

Hello all,
I have an application that inserts dates in dmy format into a database. Currently, the application is set to insert this date as a string, convert it to a datetime and finally insert it into a datetime field. This process takes place by setting the date format to dmy by using SET DATEFORMAT 'dmy'.
I would like to eliminate all this process and be able to insert the dates directly to the corresponding field. Is there a way to change the default dateformat permanently at the database level?
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

I'm not exactly sure what you are asking.

Check your servers language setting

From Books Online

SQL Server Language Support

Microsoft® SQL Server™ 2000 is installed with 33 natural languages defined on the server. The definitions for each language establish how date data is interpreted:

The formats in which dates are presented:
- dmy (day, month, year)
- mdy (month, day, year)
- ymd (year, month, day)
Short and long names for each month.
Names for each day.
Which day is considered the first day of the week.

These language definitions are stored in master.dbo.syslanguages and a language identifier (ID) identifies each language.

Each instance of SQL Server uses a default language for all connections to the server. For more information about configuring the setting, see default language Option.

Most connections use the default language configured for the server, but each connection can individually set a SQL Server language to be used for the connection:

Microsoft ActiveX® Data Object and OLE DB applications can include the Language keyword in a provider string specified when they connect.

OLE DB applications can also set the provider-specific property SSPROP_INIT_CURRENTLANGUAGE before connecting.
Open Database Connectivity (ODBC) applications can include the LANGUAGE keyword in a connection string specified on SQLDriverConnect. ODBC applications can also specify the language setting in a SQL Server ODBC data source definition.
DB-Library applications can use dblogin to allocate a loginrec, and then use the DBSETNATLANG macro to specify a language setting before calling dbopen to connect.
Any application can use the SET LANGUAGE statement to specify the SQL Server language.

SQL Server supports having multiple, language-specific copies of the error messages stored in master.dbo.sysmessages. All instances of SQL Server contain the set of English messages. SQL Server is localized, or translated, into French, German, Spanish, and Japanese versions. Installations of localized versions of SQL Server install the translated set of messages in addition to the English set. When SQL Server sends a message to a connection, it uses the localized message if the language ID of the connection matches one of the language IDs present in sysmessages. If there is no message in sysmessages with the same language ID, the English version of the message is sent.

mrivera Ten Centuries Points: 1050 More actions · Answer 2

Since I don't have access to changing the server settings, I was looking into changing the dateformat setting only for one database. This gave me the answer. I am simply including the language setting in the connection used by the application.

Thank you.