Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Setting a standard DateFormat? 3

Status
Not open for further replies.
iaresean

iaresean

Programmer
Joined
Mar 24, 2003
Messages
570
Location
ZA
Hi All;

I have worked with a couple of SQL Server 2000 databases and have run into problems when copying the database to a server on another continent or country. It seems that the local settings have an effect on how SQL Server interprets incoming dates. For e.g. Country A's server may see dates in the format MM/DD/YYYY while country B may see it as DD/MM/YYYY by default. This causes problems as a date and month with values lower than 12 most often results in mixed up dates.

I have used the 'set dateformat' function in a couple of stored procedure to combat this situation, but I was wondering if there was a way I could change the config of the database so that it always used a format of my choice (I am leaning towards (yyyy/mm/dd). If there isn't such an available configuration I would appreciate any tips on how I should format my date so that it won't run into the localization issues I have described above.

Thank you for any and all help!

Sean. [peace]
 
Sort by date Sort by votes
SQL Server doesn't mis-interpret yyyy-mm-dd values. Using that format will be understood by any SQL Server database.

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
I found a little bit of information regarding this issue that I would like to share. Before doing so, I must admit that SQLBill's advice is absolutely the way to go. However, when an app stops working properly, I can understand that there may not be sufficient time to implement his solution. So... here goes.

Each SQL Server has a default language. You can see what the default language is by executing these commands (in Query Analyzer).

[!]sp_configure 'default language'[/!]

This will tell you what the default language is (sort of). It actually returns a config_value with an integer that represents the language id.

You can then run...

[!]sp_helplanguage[/!]

You will see a list of languages that SQL Server supports.

The odd thing here is that the server's language setting will not solve your problem. This setting configures the default language for NEW users. By changing this setting, existing users will continue to have their original language. This is where it gets interesting because it's the login's language setting that determines SQL Server's date format.

For example, if user A has a default language of us_english, then a date of 4/6/2006 will be interpreted as April 4, 2006. If user B has a default language of 'British', then the date will be interpreted as June 6, 2006.

So far, I'm just presenting some background information and haven't really solved your problem.

The good news is that you can change the default language for a user so that subsequent logins will exhibit the correct interpretation of dates. Here's how:

You can set the default language for a user by issueing the following command.

[!]sp_defaultlanguage @loginame = 'LoginName', @language = 'Language'[/!]

After running this command, you will need to logout and back in to the database in order for the change to take affect. The good news is that the language setting only needs to be done once (for each user in the database).

There is an alternative method, but it only works for the current session. You can set the language in your query (much the same way the Set DateFormat works). When you disconnect from the database, the language setting is NOT saved. Set Language differs from Set DateFormat regarding weekday names and month names, for example:
Code: 
set language 'us_english'

Select Convert(DateTime, '4/6/2006'), 
       DateName(weekday, '4/6/2006'),
       DateName(Month, '4/6/2006')

set language 'Italian'

Select Convert(DateTime, '4/6/2006'), 
       DateName(Weekday, '4/6/2006'),
       DateName(Month, '4/6/2006')

Summary
You can set the default language for new logins by configuring the server's default language by using sp_configure.

You can change a user's default language by using sp_defaultlanguage.

You can temporarily change the language for a query by using Set Language

I hope you find this useful.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Fantastic piece of info there! You answered my question completely. If I could, I would give you more than one star.

I now feel completely confident in working with dates in SQL SERVER. :-)

Thank you!

Sean. [peace]
 
Upvote 0 Downvote
if user A has a default language of us_english, then a date of 4/6/2006 will be interpreted as April 4, 2006. If user B has a default language of 'British', then the date will be interpreted as June 6, 2006.
Click to expand...

George,

Shouldn't that be US interpreted as April 6, 2006 and British as 4 June 2006????

-SQLBill

Posting advice: FAQ481-4875
 
Upvote 0 Downvote
Bill,

You're right, I noticed that after posting. Thanks for clarifying.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Hi there again gmmastros... Can i ask some silly questions please?
What determines the default language during an sql server install??

If it is the language setting of the network user who installed the Db server, do you know how to change this so that new installs will be set to the required language?


Tracey
Remember... True happiness is not getting what you want...

Its wanting what you have got!
 
Upvote 0 Downvote
Tracey,

I honestly don't have a good answer for you. There are others here that will probably know.

However, you could use the stored procedure I show here to set everything to a specified language immediately after installing SQL Server.

To set the default language for new logins:

Code: 
Declare @LanguageId Int
Set @LanguageId = 0

exec sp_configure 'default language', @LanguageId
Reconfigure With Override

The 0 represents us_english. To use a different language, you will need to run sp_helplanguage to get the proper langid.

If users already exist, you can change them to your language with the following command:

[!]sp_defaultlanguage 'sa', 'us_english'[/!]


Back to your original question....
I don't know what SQL Server uses as a default. I suspect it uses the regional settings as set in the control panel's Regional and Language Options.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
Ok thanks George

I know its not the cp's Regional + Language settings, as we did a complete re-install of our OS making sure that English(New Zealand) was the only language installed (thinking that leaving the US locale was causing our grief)

our SQL server still uses us_english (0)

I would be very interested in any further info anyone could add...



Tracey
Remember... True happiness is not getting what you want...

Its wanting what you have got!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
927
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
How to add a Letter to datepart
Replies
5
Views
1K
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
773
grnzbra
grnzbra
Cpreston
  • Locked
  • Question Question
Update with a month name
Replies
9
Views
2K
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top