June 27, 2007

German Weekday

The big problem of the date-conversion "to_char (sysdate, 'D')" is, that depending on the NLS you get different results:

Sunday is the first day in the US
Monday is the first day in Germany

For the Green-Thursday of 2000 you get:

Green_Thursday := to_date ('23.03.2000', 'DD.MM.YYYY');
America: to_char (Green_Thursday, 'D') = 5
Germany: to_char (Green_Thursday, 'D') = 4

this is sub-optimal, because it's a format-mask, which is based on the underlying NLS and not on a strict rule.

My solution in this case is: The function German_Weekday


FUNCTION German_Weekday (P_Date IN DATE)
RETURN NUMBER IS
V_Delta NUMBER;
BEGIN
-- Reference-Day: Green Thursday 2000 = Day 4 in Germany
V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
'DD.MM.YYYY'),
'D')) - 4;
RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

and American_Weekday

FUNCTION American_Weekday (P_Date IN DATE)
RETURN NUMBER IS
V_Delta NUMBER;
BEGIN
-- Reference-Day: Green Thursday 2000 = Day 5 in the US
V_Delta := TO_NUMBER (TO_CHAR (TO_DATE ('23.03.2000',
'DD.MM.YYYY'),
'D')) - 5;
RETURN (TO_NUMBER (TO_CHAR (P_Date-V_Delta, 'D')));
END;

this function always returns the correct german 'D'-formatmask, indepent of the NLS.

have fun and use it
Gerd

6 comments:

Jens Petersen said...

Your functions won't return the correct result for some other territory settings.
Take for example MOROCCO:

SQL> alter session set nls_territory='MOROCCO';

Session altered.

SQL> select to_char(to_date ('23.03.2000', 'DD.MM.YYYY'), 'D') from dual;

T
-
6

Gerd Volberg said...

Thanks Jens, you found a problem! The new code now works with a delta-day, which solves this problem.

jwh said...

select mod(to_number(to_char(sysdate,'J')),7) from dual

each day always has the same number, anywhere.

Gerd Volberg said...

The julian format gives you a constant value back. The tuesday is 1, wednesday = 2, ...
After this conversion you have to use an offset to get the correct german or american format.
The select produces a database I/O, which is not so fast as the function.

Laurent Schneider said...

To have monday=1, I use trunc(d)-trunc(d,'iw')+1

ex:
select
trunc(sysdate)-trunc(sysdate,'iw')+1,
to_char(sysdate,'Day')
from dual;
2 Tuesday

which 100% nls independant...

Lars Bo Nielsen said...

Thanks Laurent, just what I needed.