Calendar used by SQL Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calendar used by SQL Server

Author

Topic

jones_d
Yak Posting Veteran

61 Posts

Posted - 2005-01-14 : 16:06:55

Hi,

I am running the following sql against 2 databases:


set dateformat ymd
select datepart(week,'2006-01-02 00:00:00')

In 1 database I get 1 as the result. In the other I get 2 as the result. Since both databases are returning different results I assume that they are using different calendars.

Does anyone know how I can find out what calendar SQL Server is using?
Is it possible to change this?

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2005-01-14 : 16:43:08

Or could the issue be related to the Language... mdy other languages say dmy check what the result is when you run somethin like 2006-01-31 versus 2006-31-01

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-14 : 17:46:53

see in bol SET DATEFIRST;
obviously this option has different values in the two databases

--- set datefirst 1 --- 2
set dateformat ymd
select datepart(ww,'2006-01-02 00:00:00'), @@datefirst

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-15 : 09:01:51

1.
On what depends how SS will interprete some string representation of a date(time)?
2.
Say, SS has received and has to "parse" the select: select datepart(ww,'2006-01-02')
3.
Note: it absolutely does not matter from where this select was sent to the SS.
It might be QA, or some VB-ADO code or whatever you like.
4.
So, how SS should interprete '2006-01-02'? As Jan 02 or as Feb 01?
5.
The answer is: it depends ONLY on the default_language of the login under which the
select statement was issued.

In QA, I (sa) check my personal settings:

select @@datefirst
select @@language

----
7
----------
us_english

Means, 'for me' Week begins on Sundays, and 'my' dateformat is mdy (or yyyymd).
So, the result of select datepart(ww,'2006-01-02') will be:

---
1

Now I want to change my preferences and I run this exec:

exec sp_defaultlanguage 'sa', 'french'

From now (after re-connecting) my settings (in all my sessions, in all available
databases) will be:

select @@datefirst
select @@language
select datepart(ww,'2006-01-02')

----
1
----------
french
---
6

Of course, SET DATEFORMAT and SET DATEFIRST will still work for me but only
during one session.
Note a special case of datetime strings without separators (/, -, .). SS ALWAYS
interpretes them as ymd. Exs: '20040131', '050131'.

To see correspondence LANGUAGE - DATEFORMAT - DATEFIRST run
select langid, dateformat, datefirst, name from master..syslanguages


langid dateformat datefirst name      
------ ---------- --------- ------------- 
0      mdy        7         us_english
1      dmy        1         Deutsch
2      dmy        1         Francais
3      ymd        7         ???
4      dmy        1         Dansk
5      dmy        1         Espanol
6      dmy        1         Italiano
7      dmy        1         Nederlands
8      dmy        1         Norsk
9      dmy        7         Portugues
10     dmy        1         Suomi
11     ymd        1         Svenska
12     dmy        1         cestina
13     ymd        1         magyar
14     dmy        1         polski
15     dmy        1         romana
16     ymd        1         hrvatski
17     dmy        1         slovencina
18     dmy        1         slovenski
19     dmy        1         ????????
20     dmy        1         áúëãàðñêè
21     dmy        1         ðóññêèé
22     dmy        1         Turkce
23     dmy        1         British

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-15 : 17:45:14

6.
There is no such thing as DATEFORMAT & DATEFIRST for this or that given database.
Suppose,
I created the following view:

use myDB
GO
create view vw as select datepart(ww,'31-01-2006')
GO

Can you tell is this view correct or not??? No, you can't. The question itself is incorrect.

If language of my login is French then select * from vw will work fine for me,

but if it's US_English then I will get an data type conversion error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources