SQL Server - difference between week number on two separate machines

Question

Hi All,

Hopefully someone can help me out.

I have recently been asked to move several databases from a really old spec machine to a slightly less old spec machine.  After "successfully" moving the databases and running both servers in tandem for several weeks one of the users noticed an anomaly between two databases on the two separate servers.  The anomaly was to do with the "Week Number", i.e the old machine appears to be starting "Week 2" on '07-01-09' and the new machine on '08-01-09' .

The old machine is running Windows 2000 and the new machine is running Windows 2003 and this is the only difference I see.  When I do the following query, it returns slightly different data for Week Number.

SELECT COUNT(*) AS 'Num Consignments',
       CONS_DATE AS 'Consignment Date',
       DATEPART(week, CONS_DATE) AS 'Week Number',
       DATEPART(month, CONS_DATE) AS 'Month Number',
       DATEPART(quarter, CONS_DATE) AS 'Quarter Number',
       DATEPART(year, CONS_DATE) AS 'Year Number'
FROM CONSIGNMENTS GROUP BY CONS_DATE
               ORDER BY CONS_DATE

Old Server Results

Num Consignments	Consignment Date	Week Number	Month Number	Quarter Number	Year Number
37	01/01/2007	1	1	1	2007
1967	02/01/2007	1	1	1	2007
2641	03/01/2007	1	1	1	2007
2652	04/01/2007	1	1	1	2007
2689	05/01/2007	1	1	1	2007
176	06/01/2007	1	1	1	2007
23	07/01/2007	2	1	1	2007
3182	08/01/2007	2	1	1	2007
2762	09/01/2007	2	1	1	2007
2904	10/01/2007	2	1	1	2007
3283	11/01/2007	2	1	1	2007
2954	12/01/2007	2	1	1	2007
101	13/01/2007	2	1	1	2007
16	14/01/2007	3	1	1	2007
3351	15/01/2007	3	1	1	2007
2961	16/01/2007	3	1	1	2007
2921	17/01/2007	3	1	1	2007
2940	18/01/2007	3	1	1	2007
2792	19/01/2007	3	1	1	2007
128	20/01/2007	3	1	1	2007
18	21/01/2007	4	1	1	2007

New Server Results

Num Consignments	Consignment Date	Week Number	Month Number	Quarter Number	Year Number
37	01/01/2007	1	1	1	2007
1967	02/01/2007	1	1	1	2007
2641	03/01/2007	1	1	1	2007
2652	04/01/2007	1	1	1	2007
2689	05/01/2007	1	1	1	2007
176	06/01/2007	1	1	1	2007
23	07/01/2007	1	1	1	2007
3182	08/01/2007	2	1	1	2007
2762	09/01/2007	2	1	1	2007
2904	10/01/2007	2	1	1	2007
3283	11/01/2007	2	1	1	2007
2954	12/01/2007	2	1	1	2007
101	13/01/2007	2	1	1	2007
16	14/01/2007	2	1	1	2007
3351	15/01/2007	3	1	1	2007
2961	16/01/2007	3	1	1	2007
2921	17/01/2007	3	1	1	2007
2940	18/01/2007	3	1	1	2007
2792	19/01/2007	3	1	1	2007
128	20/01/2007	3	1	1	2007
18	21/01/2007	3	1	1	2007
3207	22/01/2007	4	1	1	2007

The values for @@DATEFIRST and so on are identical on each server, so I'm thinking it may be something to do with Windows 2000 and Windows 2003 configuration.  I've set up the environment variables on both servers to be the same, i.e. date etc in Regional Settings in the control control panel and cannot see any further differences.

Any help would be much appreciated.

Cheers,

David

Answer 1

Are you sure the language settings of both servers are the same?

Run this on both: 

select @@LANGUAGE

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 2

Hi Phil,

Thanks for the reply;  I've done that and both servers return "British".  I'm just looking at the SQL Server Books Online to see if there's anything in the DB Library Options. 

By the way, I don't know if I mentioned, but I'm running SQL Server 2000 Standard Edition on both machines and I've noticed that there's a difference in the O/S, the new machine being Windows NT - 5.2 (3790), Product Version 8.00.2039(SP4) and the old machine being Windows NT - 5.O (2195), Product Version 8.00.763(SP3).

Cheers,

David

Answer 3

Hi Phil,

Thanks for the reply;  I've done that and both servers return "British".  I'm just looking at the SQL Server Books Online to see if there's anything in the DB Library Options. 

By the way, I don't know if I mentioned, but I'm running SQL Server 2000 Standard Edition on both machines and I've noticed that there's a difference in the O/S, the new machine being Windows NT - 5.2 (3790), Product Version 8.00.2039(SP4) and the old machine being Windows NT - 5.O (2195), Product Version 8.00.763(SP3).

Cheers,

David

I've just done a dbcc useroptions on both servers.  One shows date format as dmy, the other mdy.  I think that this may have something to do with it.

Answer 4

Hi Phil,

Thanks for the reply;  I've done that and both servers return "British".  I'm just looking at the SQL Server Books Online to see if there's anything in the DB Library Options. 

By the way, I don't know if I mentioned, but I'm running SQL Server 2000 Standard Edition on both machines and I've noticed that there's a difference in the O/S, the new machine being Windows NT - 5.2 (3790), Product Version 8.00.2039(SP4) and the old machine being Windows NT - 5.O (2195), Product Version 8.00.763(SP3).

Cheers,

David

I've just done a dbcc useroptions on both servers.  One shows date format as dmy, the other mdy.  I think that this may have something to do with it.

Update - problem solved.

Firstly, Phil, thanks for your assistance.  The @@LANGUAGE from Query Analyzer gave me a bit of a clue and I did't know until I RTFM that this will return the user's default setting and not the system default login.  My default login matched on both servers and hence returned the same value.

When I logged in as the account that was having the problems, the same was not true. However, this still didn't solve the problem.

To solve the problem, I "customised" some of the parameters (i.e. short date format, separator and so on) for the default language of the new machine (using Control Panel, Regional and Language Options) to match that of the old machine.  Previously the sys admin guy attempted this but didn't tick the "apply to current and default accounts" box.  Fortunately I "found" that little check box and ticked it.

After changing the default to "English (United States)" this set language to "us_english" and datefirst to 7 for the default user.  Finally, I had to go into Enterprise Manager, Security, Logins and change the Default Language of the user profile from its default of "British English" to <default> (I tried "English" but that didn't work as it didn't pick up the little changes I'd made to the "Regional and Language Options").

When I re-booted the new machine and logged in as the user the default config of the server was picked up and hey presto, the queries on both machines matched.

I'm sure there was probably a quicker way around this, but hey-ho, it worked.

Answer 5

I don't know if this is a TSQL question is it?  I thought that it was more a config type question hence the reason for me putting it in the SQL Server Database Engine forum.