ORACLE SQL RETURNS WRONG DATA ON WINDOWS 2003 SERVER

[TTEL]

We have a query which runs in an Excel spreadsheet and returns a date based on an Oracle query through ODBC.
The query contains a DECODE statement and is shown below for reference.

What the issue is is that the program has been running quite happily on Windows XP and Windows 2000 Server but as soon as we move it to Windows 2003 server the dates are wrong.

I believe it is to do with the fact that the decode statement sees the days of the week differently on this server. i.e. On XP/Win 2k sunday is day '1' and on Win2003 it seems to be day '3'. Therefore the decode returns wrong dates.

How do we make this Win 2003 server see Sunday as day '1' for the purposes of the decode?

We have checked ODBC, Oracle, Excel versions and they are identical on all machines therefore pointing to the Windows 2003 O/S software as the culprit.

Any help appreciated!

SELECT MBG140.ACCOUNT15_ITM, MBG140.SORDNO_ITM||'-'||MBG140.SORDITEM SORDER,
MBF010.WORDNOFST||'/'||MBF010.WORDNO WORDER,
DECODE(TO_CHAR(MBF010.WORDUE,'D'),
1,SUBSTR(MBF010.WORDUE+3,1,10),
2,SUBSTR(MBF010.WORDUE+3,1,10),
3,SUBSTR(MBF010.WORDUE+5,1,10),
4,SUBSTR(MBF010.WORDUE+5,1,10),
5,SUBSTR(MBF010.WORDUE+5,1,10),
6,SUBSTR(MBF010.WORDUE+3,1,10),
7,SUBSTR(MBF010.WORDUE+3,1,10)) WORDUE,
DECODE(TO_CHAR(MBF010.WORDPROM,'D'),
1,SUBSTR(MBF010.WORDPROM+3,1,10),
2,SUBSTR(MBF010.WORDPROM+3,1,10),
3,SUBSTR(MBF010.WORDPROM+5,1,10),
4,SUBSTR(MBF010.WORDPROM+5,1,10),
5,SUBSTR(MBF010.WORDPROM+5,1,10),
6,SUBSTR(MBF010.WORDPROM+3,1,10),
7,SUBSTR(MBF010.WORDPROM+3,1,10)) WORDPROM,
MBF010.WORDQTY, MBF010.WORDQTYDEL,
MBF010.WORDSTAT,
MBF010.PROCSTAGE_LAST
FROM LIVEDB.MBF010 MBF010,
LIVEDB.MBG140 MBG140,
LIVEDB.MBG340 MBG340
WHERE MBF010.ACCOUNT15_WOR = MBG340.ACCOUNT15_SOX
AND MBF010.WORDNO = MBG340.WORDNO_SOX
AND MBF010.WORDNOSUF = MBG340.WORDNOSUF_SOX
AND MBG340.ACCOUNT15_SOX = MBG140.ACCOUNT15_ITM
AND MBG340.SORDNO_SOX = MBG140.SORDNO_ITM
AND MBG340.SORDITEM_SOX = MBG140.SORDITEM
AND MBF010.ACCOUNT15_WOR='TTEL TTEL TTEL'
AND MBF010.PARTNO_WOR Like 'FFM%'
AND MBF010.UOM_WOR='M'
AND WORDQTY-WORDQTYDEL>=50
AND MBF010.WORDSTAT<>'0'
AND MBF010.WORDREF1 LIKE '%GORE%'
and wordno='423820'
ORDER BY MBG140.SORDNO_ITM||'-'||MBG140.SORDITEM

 

[hoinz]

The date format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.

So it seems parameter NLS_TERRITORY has to be the same, too.
hope this helps

 

[hoinz]

thread186-492508 might help