I want to calculate the last day of the month, but if this date is a sunday then the below query should not give any output.
SELECT * FROM DUAL WHERE LAST_DAY(SYSDATE) = SYSDATE
I am using it in a trigger hence I only want the above query it give an output when the last day of the month is not a sunday. Which function to use to get the day of the month or to tweak this query ?
CodePudding user response：
Like this:
select 1
from dual
where to_char(last_day(sysdate),'D') != 1;
Depending on your NLS-settings you may have to change the constant or use an NLS-parameter in to_char().
CodePudding user response：
There are 2 common ways to check the day of week:
- Most obvious and simple thing is to specify you nls_date_language and use weekday names:
SQL> select to_char(sysdate,'day','nls_date_language=English') from dual;
TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANG
------------------------------------
friday
1 row selected.
SQL> select to_char(sysdate,'day','nls_date_language=French') from dual;
TO_CHAR(SYSDATE,'DAY','NLS_DATE_
--------------------------------
vendredi
1 row selected.
So you can use
SQL> select 'ok' x from dual where to_char(sysdate,'fmday','nls_date_language=English')='friday';
X
--
ok
1 row selected.
NB. Do not forget to use 'fm' to trim white spaces, ie 'friday' and not 'friday ':
SQL> select '['||to_char(sysdate,'day','nls_date_language=English')||']' from dual;
'['||TO_CHAR(SYSDATE,'DAY','NLS_DATE_L
--------------------------------------
[friday ]
1 row selected.
SQL> select '['||to_char(sysdate,'fmday','nls_date_language=English')||']' from dual;
'['||TO_CHAR(SYSDATE,'FMDAY','NLS_DATE
--------------------------------------
[friday]
1 row selected.
- Another way is to use numeric day format, but in this case your day number depends on nls_terrory, for example:
DBFiddle: https://dbfiddle.uk/o4i3rlBp
SQL> alter session set nls_territory=America;
Session altered.
with first_10_of_2020 as (
select date'2020-01-01' level-1 dt from dual connect by level<=10
)
select
to_char(dt,'yyyy-mm-dd') dt
,to_char(dt,'day','nls_date_language=''English''') day_en
,to_char(dt, 'd', 'nls_date_language=''NUMERIC DATE LANGUAGE''') d_1
from first_10_of_2020;
DT DAY_EN D
---------- ------------------------------------ -
2020-01-01 wednesday 4
2020-01-02 thursday 5
2020-01-03 friday 6
2020-01-04 saturday 7
2020-01-05 sunday 1
2020-01-06 monday 2
2020-01-07 tuesday 3
2020-01-08 wednesday 4
2020-01-09 thursday 5
2020-01-10 friday 6
10 rows selected.
SQL> alter session set nls_territory=France;
Session altered.
with first_10_of_2020 as (
select date'2020-01-01' level-1 dt from dual connect by level<=10
)
select
to_char(dt,'yyyy-mm-dd') dt
,to_char(dt,'day','nls_date_language=''English''') day_en
,to_char(dt, 'd', 'nls_date_language=''NUMERIC DATE LANGUAGE''') d_1
from first_10_of_2020;
DT DAY_EN D
---------- ------------------------------------ -
2020-01-01 wednesday 3
2020-01-02 thursday 4
2020-01-03 friday 5
2020-01-04 saturday 6
2020-01-05 sunday 7
2020-01-06 monday 1
2020-01-07 tuesday 2
2020-01-08 wednesday 3
2020-01-09 thursday 4
2020-01-10 friday 5
10 rows selected.
As you can see in case of NLS_TERRITORY=America, Friday is 6, while in case of France it's 5. So to make it more reliable you can use ISO weeks, ie you can use > 'IW' format model:
IW: Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard.
A calendar week starts on Monday.
The first calendar week of the year includes January 4.
The first calendar week of the year may include December 29, 30 and 31.
The last calendar week of the year may include January 1, 2, and 3.
So you can use trunc(dt)-trunc(dt,'iw') 1 and it doesn't depend on your nls_language/nls_territory: Monday is always 1 and Sunday is always 7:
DBFiddle: https://dbfiddle.uk/zVS2vcjK
SQL> alter session set nls_territory=America;
Session altered.
with first_10_of_2020 as (
select date'2020-01-01' level-1 dt from dual connect by level<=10
)
select
to_char(dt,'yyyy-mm-dd') dt
,to_char(dt,'day','nls_date_language=''English''') day_en
,trunc(dt)-trunc(dt,'iw') 1 as d_1
from first_10_of_2020;
DT DAY_EN D_1
---------- ------------------------------------ -----
2020-01-01 wednesday 3
2020-01-02 thursday 4
2020-01-03 friday 5
2020-01-04 saturday 6
2020-01-05 sunday 7
2020-01-06 monday 1
2020-01-07 tuesday 2
2020-01-08 wednesday 3
2020-01-09 thursday 4
2020-01-10 friday 5
10 rows selected.
SQL> alter session set nls_territory=France;
Session altered.
with first_10_of_2020 as (
select date'2020-01-01' level-1 dt from dual connect by level<=10
)
select
to_char(dt,'yyyy-mm-dd') dt
,to_char(dt,'day','nls_date_language=''English''') day_en
,trunc(dt)-trunc(dt,'iw') 1 as d_1
from first_10_of_2020;
DT DAY_EN D_1
---------- ------------------------------------ -----
2020-01-01 wednesday 3
2020-01-02 thursday 4
2020-01-03 friday 5
2020-01-04 saturday 6
2020-01-05 sunday 7
2020-01-06 monday 1
2020-01-07 tuesday 2
2020-01-08 wednesday 3
2020-01-09 thursday 4
2020-01-10 friday 5
10 rows selected.