how to get monday=1, tuesday=2…
February 20th, 2008
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]
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
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
Thanx for the explanation Laurent.
I check your blog a lot, so keep on blogging!
sure thing