Categories
Blogroll sql

how to get monday=1, tuesday=2…

I wrote it already :
Laurent Schneider » Blog Archive » TO_CHAR(…, ‘D’)

I thought the one I found out this night was fun
select to_char(sysdate,'DAY',
'NLS_DATE_LANGUAGE=''numeric date language''')
from dual;
T
-
3

[edit]this feature is not documented, do not use in prod[/edit]

By Laurent Schneider

Oracle Certified Master

14 replies on “how to get monday=1, tuesday=2…”

Laurent,
I know it is not about your post, but wonder if you can help me.
How can I determine first business day of the month passing in sysdate?
Thanks,
Eugene

select to_char(trunc(sysdate,’mm’)+decode(TO_CHAR(TRUNC(sysdate,’MM’),’DY’,’NLS_DATE_LANGUAGE=AMERICAN’),’SAT’,2,’SUN’,1,0),’DL’) from dual;
TO_CHAR(TRUNC(SYSDATE,’MM’)+D
—————————–
Friday, February 01, 2008

Laurent,
Thanks for reply, but …
SQL> select to_char(trunc(sysdate,’mm’) + decode(TO_CHAR(TRUNC(sysdate,’MM’),’DY’,’NLS_DATE_LANGUAGE
=AMERICAN’),’SAT’,2,’SUN’,1,0),’DL’) from dual;
select to_char(trunc(sysdate,’mm’) + decode(TO_CHAR(TRUNC(sysdate,’MM’),’DY’,’NLS_DATE_LANGUAGE=AMER

ERROR at line 1:
ORA-01821: date format not recognized

Eugene

try to upgrade to a supported release of Oracle 8)

the formatting is not necessary

select trunc(sysdate,’mm’)+decode(TO_CHAR(TRUNC(sysdate,’mm’),’DY’,’NLS_DATE_LANGUAGE=AMERICAN’),’SAT’,2,’SUN’,1,0) from dual;

Interestingly, there’s a difference in the way “numeric date language” and the “D” format handle territory.

> alter session set nls_territory=’America’;

Session altered.

> select to_char(sysdate,’DAY’,’NLS_DATE_LANGUAGE=”numeric date language”’),
2 to_char(sysdate,’DAY D’) from dual;
T TO_CHAR(SYS
– ———–
4 THURSDAY 5

> alter session set nls_territory=’Australia’;

Session altered.

> select to_char(sysdate,’DAY’,’NLS_DATE_LANGUAGE=”numeric date language”’),
2 to_char(sysdate,’DAY D’) from dual;
T TO_CHAR(SYS
– ———–
4 THURSDAY 4

For the month passing:

SELECT
CASE
WHEN TO_CHAR(A, ‘D’) IN (7, 1) THEN NEXT_DAY(A, 2)
ELSE A
END
FROM
(SELECT TRUNC(ADD_MONTHS(SysDate, +1), ‘MM’) A FROM Dual);

Of course, the country’s specifics require manual intervention. 🙂

this is all the point about using the post… Do not use ‘D’ !

Unfortunately you have no NLS_DATE_TERRITORY, therefore you cannot do something like

select to_char(sysdate,’D’, ‘nls_date_territory=switzerland’), to_char(sysdate,’DL’,’nls_date_territory=america’) from dual;

it is simply impossible. But be patient, I have raised an enhancement request 4 years ago
bug 3769415

Ah, now i see what you did. *slaps self on head*

I could change the D to Dy, but its jsut a tradeoff of the more clear choice of using CASE/DECODE to choose the day, or to add the required amount of days. Personally, i think NEXT_DAY is a lot quite clearer.

next_day is also session dependent. having a session dependent statement is way more uncomfortable.
1) you cannot use it in materialized view
2) you cannot use it in function based index
3) you cannot have different behavior within the same statement, like displaying the default local currency, the American day of week, and the swiss short time TS (I am quite busy those days while writing about it in my book

About Dy, just to make you worry, it may changed as in changed in French from 9.2.0.8 to 10.1.0.5 from 2 chars to 4 chars abbreviation (imagine the hassle if it were hard coded in your program!) 😈

Well, I never use NEXT_DAY as a large number of my customers do have different locale settings. However I admit that if you never used and will never anything than american_america, then why bother, go for NEXT_DAY 😎

[…] Laurent Schneider […]

Use SQL to Create a Calendar in Mutliple Languages | Oracle Administrators Blog - by Aman Soodsays:

[…] Laurent Schneider […]

Leave a Reply

Your email address will not be published.