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:
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
Thanks Jens, you found a problem! The new code now works with a delta-day, which solves this problem.
select mod(to_number(to_char(sysdate,'J')),7) from dual
each day always has the same number, anywhere.
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.
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...
Thanks Laurent, just what I needed.
Post a Comment