Reputation: 87
TO_CHAR returning incorrect results when comparing to date string in Oracle
Im trying to write a query which gets the first business day of the month of the current year. The rules are if the 1st of the month is a Saturday/Sunday then take the following Monday's date. Additionally if it is 1st January in any case always take the next working date (Friday, Saturday, Sunday take the following Monday, and all other cases take the next working day).
The logic works fine from February to December but for January it is returning 2 rows for the date calculation. One which is correct and one which is incorrect.
The problem I think is coming from, when Im trying to compare the the sysdate with '01.01'.
Please find my workings below. All data is calculated using dual so this can be run without creating additional tables.
You will see 2 rows are returned for January (first is incorrect and second is correct):
BD_DATE DAY_NUM ADD_DAYS DATE_CALC
--------- ------- ---------- ---------
01-JAN-20 4 0 01-JAN-20
01-JAN-20 4 1 02-JAN-20
01-FEB-20 7 2 03-FEB-20
01-MäR-20 1 1 02-MäR-20
01-APR-20 4 0 01-APR-20
01-MAI-20 6 0 01-MAI-20
01-JUN-20 2 0 01-JUN-20
01-JUL-20 4 0 01-JUL-20
01-AUG-20 7 2 03-AUG-20
01-SEP-20 3 0 01-SEP-20
01-OKT-20 5 0 01-OKT-20
01-NOV-20 1 1 02-NOV-20
01-DEZ-20 3 0 01-DEZ-20
13 rows selected.
My SQL query is:
/* Formatted on 01.04.2020 18:37:56 (QP5 v5.163.1008.3004) */
WITH dateparam
AS ( SELECT TRUNC (SYSDATE, 'YYYY') + LEVEL - 1 AS mydate
FROM DUAL
CONNECT BY TRUNC (TRUNC (SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') =
TRUNC (SYSDATE, 'YYYY'))
SELECT DISTINCT
ADD_MONTHS (LAST_DAY (mydate) + 1, -1) bd_date,
TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') day_num,
/*TO_CHAR (mydate, 'DD.MM') IN ('01.01') THEN
(DECODE (
(to_char(add_months(last_day(mydate)+1,-1), 'fmD')),
6, 3,
7, 2,
1, 1,
1))
ELSE*/
CASE
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
THEN
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
6
THEN
3
ELSE
1
END
ELSE
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
1
THEN
1
ELSE
0
END
END
add_days,
ADD_MONTHS (LAST_DAY (mydate) + 1, -1)
+ CASE
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
THEN
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
6, 3,
7, 2,
1, 1,
1))
ELSE
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
7, 2,
1, 1,
0))
END
date_calc
FROM dateparam
ORDER BY 1;
I have tried using both DECODEand CASEand both blocks return the same results.
Any ideas what I have done wrong?
Upvotes: 0
Views: 1328
Answers (3)
Reputation: 167982
TO_CHAR relies on the NLS settings including territory and language. If you want to do the calculation independently of the NLS settings then you can use the fact that an ISO week always starts on a Monday and calculate the number of days difference between the first day of the month and the start (Monday) of the ISO week containing that first day of the month:
WITH months ( first_day ) AS (
SELECT CASE LEVEL
WHEN 1 THEN TRUNC( SYSDATE, 'YYYY' ) + INTERVAL '1' DAY
ELSE ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), LEVEL - 1 )
END
FROM DUAL
CONNECT BY LEVEL <= 12
)
SELECT first_day
+ CASE first_day - TRUNC( first_day, 'IW' )
WHEN 5 THEN 2 -- Saturday
WHEN 6 THEN 1 -- Sunday
ELSE 0 -- Weekday
END
AS first_business_day
FROM months;
Which outputs:
| FIRST_BUSINESS_DAY | | :----------------- | | 2020-01-02 (THU) | | 2020-02-03 (MON) | | 2020-03-02 (MON) | | 2020-04-01 (WED) | | 2020-05-01 (FRI) | | 2020-06-01 (MON) | | 2020-07-01 (WED) | | 2020-08-03 (MON) | | 2020-09-01 (TUE) | | 2020-10-01 (THU) | | 2020-11-02 (MON) | | 2020-12-01 (TUE) |
db<>fiddle here
Any ideas what I have done wrong?
The query is too complicated. You are generating all the days of the year in the sub-query factoring (WITH) clause when you are interested in only the first days of the month and this means that in the second part of the query you are replacing each day with the first of the month using:
ADD_MONTHS (LAST_DAY (mydate) + 1, -1)
Which could be simplified to just:
TRUNC( mydate, 'MM' )
Or, if you just generated in incrementing months to start with then you wouldn't even need truncate as you would only have rows for the first day of the month:
WITH months ( first_day ) AS (
SELECT ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), LEVEL - 1 )
FROM DUAL
CONNECT BY LEVEL <= 12
)
But, leaving that aside, your add_days case statement checks whether the date is the first of the month and then applies two different sets of logic to days that are or aren't the first of the month:
That section of your query can be written more simply as:
CASE
WHEN TO_CHAR (mydate, 'DD.MM') = '01.01'
THEN CASE TO_CHAR( TRUNC( mydate, 'MM' ), 'fmD' )
WHEN '7' THEN 2
WHEN '6' THEN 3
ELSE 1
END
ELSE CASE TO_CHAR( TRUNC( mydate, 'MM' ), 'fmD' )
WHEN '7' THEN 2
WHEN '1' THEN 1 -- Different to the above
ELSE 0 -- Again, different.
END
END AS add_days
You will add a different number of days depending on whether the input date is the first day of the month or not and this is why you are getting duplicate rows for some months.
You query should (again, ignoring that you are generating too many rows) be something like:
WITH dateparam ( mydate ) AS (
SELECT TRUNC(SYSDATE, 'YYYY') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), 12 ) - TRUNC( SYSDATE, 'YYYY' )
)
SELECT DISTINCT
TRUNC( mydate, 'MM' ) bd_date,
TO_CHAR( TRUNC( mydate, 'MM' ) , 'fmD') day_num,
CASE TO_CHAR( TRUNC( mydate, 'MM' ), 'fmD')
WHEN '7' THEN 2
WHEN '1' THEN 1
ELSE 0
END add_days,
TRUNC( mydate, 'MM' )
+ CASE TO_CHAR( TRUNC( mydate, 'MM' ), 'fmD')
WHEN '7' THEN 2
WHEN '1' THEN 1
ELSE 0
END date_calc
FROM dateparam
ORDER BY 1;
Which will work in any territory where the first day of the week is a Sunday ... which is not large swathes of the world. In those other places, your query will give an incorrect answer and, instead, if Monday is the first day of the week would shift Sunday and Monday to Tuesday.
For example:
ALTER SESSION SET NLS_TERRITORY = 'America';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD "("DY")"';
and the above query outputs:
BD_DATE | DAY_NUM | ADD_DAYS | DATE_CALC :--------------- | :------ | -------: | :--------------- 2020-01-01 (WED) | 4 | 0 | 2020-01-01 (WED) 2020-02-01 (SAT) | 7 | 2 | 2020-02-03 (MON) 2020-03-01 (SUN) | 1 | 1 | 2020-03-02 (MON) 2020-04-01 (WED) | 4 | 0 | 2020-04-01 (WED) 2020-05-01 (FRI) | 6 | 0 | 2020-05-01 (FRI) 2020-06-01 (MON) | 2 | 0 | 2020-06-01 (MON) 2020-07-01 (WED) | 4 | 0 | 2020-07-01 (WED) 2020-08-01 (SAT) | 7 | 2 | 2020-08-03 (MON) 2020-09-01 (TUE) | 3 | 0 | 2020-09-01 (TUE) 2020-10-01 (THU) | 5 | 0 | 2020-10-01 (THU) 2020-11-01 (SUN) | 1 | 1 | 2020-11-02 (MON) 2020-12-01 (TUE) | 3 | 0 | 2020-12-01 (TUE)
but:
ALTER SESSION SET NLS_TERRITORY = 'France';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD "("DY")"';
and it outputs:
BD_DATE | DAY_NUM | ADD_DAYS | DATE_CALC :--------------- | :------ | -------: | :--------------- 2020-01-01 (WED) | 3 | 0 | 2020-01-01 (WED) 2020-02-01 (SAT) | 6 | 0 | 2020-02-01 (SAT) 2020-03-01 (SUN) | 7 | 2 | 2020-03-03 (TUE) 2020-04-01 (WED) | 3 | 0 | 2020-04-01 (WED) 2020-05-01 (FRI) | 5 | 0 | 2020-05-01 (FRI) 2020-06-01 (MON) | 1 | 1 | 2020-06-02 (TUE) 2020-07-01 (WED) | 3 | 0 | 2020-07-01 (WED) 2020-08-01 (SAT) | 6 | 0 | 2020-08-01 (SAT) 2020-09-01 (TUE) | 2 | 0 | 2020-09-01 (TUE) 2020-10-01 (THU) | 4 | 0 | 2020-10-01 (THU) 2020-11-01 (SUN) | 7 | 2 | 2020-11-03 (TUE) 2020-12-01 (TUE) | 2 | 0 | 2020-12-01 (TUE)
db<>fiddle here
You either need to ensure that the NLS_TERRITORY setting never changes (however, ANY user can set it to whatever value they want in their session at any time) or use something that is agnostic to the NLS settings (like my example at the top). You cannot fix the query with the D format model by using the third argument of TO_CHAR as it only lets you set the NLS_DATE_LANGUAGE (which has no effect on the first day of the week) and does not accept an NLS_TERRITORY (which does control the first day of the week) but you could use the DY format model if you were really fixated on using TO_CHAR. I.e.:
CASE TO_CHAR( TRUNC( mydate, 'MM' ), 'DY', 'NLS_DATE_LANGUAGE=American' )
WHEN 'SAT' THEN 2
WHEN 'SUN' THEN 1
ELSE 0
END add_days
Upvotes: 2
Reputation: 87
I found the issue. Its nothing to do with NLS but rather with the column name im passing to the to_char. Instead of mydateI should've been substituting it with bd_date.
To show my working:
WITH dateparam
AS ( SELECT TRUNC (SYSDATE, 'YYYY') + LEVEL - 1 AS mydate
FROM DUAL
CONNECT BY TRUNC (TRUNC (SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') =
TRUNC (SYSDATE, 'YYYY'))
SELECT
mydate,
ADD_MONTHS (LAST_DAY (mydate) + 1, -1) bd_date,
TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') day_num,
CASE
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
THEN
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
6
THEN
3
ELSE
1
END
ELSE
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
1
THEN
1
ELSE
0
END
END
add_days
/*ADD_MONTHS (LAST_DAY (mydate) + 1, -1)
+ CASE
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
THEN
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
6, 3,
7, 2,
1, 1,
1))
ELSE
(DECODE (
(TO_CHAR (ADD_MON(LAST_DAY (mydate) + 1, -1), 'fmD')),
7, 2,
1, 1,
0))
END
date_calc*/
FROM dateparam where mydate <= '03JAN2020'
ORDER BY 1;
Yields:
MYDATE BD_DATE DAY_NUM ADD_DAYS
--------- --------- ------- ----------
01-JAN-20 01-JAN-20 4 1
02-JAN-20 01-JAN-20 4 0
03-JAN-20 01-JAN-20 4 0
3 rows selected.
The problem was in my original code:
WHEN TO_CHAR (mydate, 'DD.MM') IN ('01.01')
This does the calculation for 01.01 and 02.01 but the bd_date is the same for all the days in January. So once I do a distincton all the rows in January I will end up with the two rows as shown above, once I remove the column mydate.
The corrected comparison should be on bd_date instead:
WHEN TO_CHAR ( ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'DD.MM') IN ('01.01') --Get bd_date using mydate
Working code:
/* Formatted on 02.04.2020 10:42:53 (QP5 v5.163.1008.3004) */
WITH dateparam
AS ( SELECT TRUNC (SYSDATE, 'YYYY') + LEVEL - 1 AS mydate
FROM DUAL
CONNECT BY TRUNC (TRUNC (SYSDATE, 'YYYY') + LEVEL - 1, 'YYYY') =
TRUNC (SYSDATE, 'YYYY'))
SELECT DISTINCT
ADD_MONTHS (LAST_DAY (mydate) + 1, -1) bd_date,
TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') day_num,
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'DD.MM') IN
('01.01')
THEN
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
6
THEN
3
ELSE
1
END
ELSE
CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
7
THEN
2
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD') =
1
THEN
1
ELSE
0
END
END
add_days,
ADD_MONTHS (LAST_DAY (mydate) + 1, -1)
+ CASE
WHEN TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'DD.MM') IN
('01.01')
THEN
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
6, 3,
7, 2,
1, 1,
1))
ELSE
(DECODE (
(TO_CHAR (ADD_MONTHS (LAST_DAY (mydate) + 1, -1), 'fmD')),
7, 2,
1, 1,
0))
END
date_calc
FROM dateparam
ORDER BY 1;
Results:
BD_DATE DAY_NUM ADD_DAYS DATE_CALC
--------- ------- ---------- ---------
01-JAN-20 4 1 02-JAN-20
01-FEB-20 7 2 03-FEB-20
01-MäR-20 1 1 02-MäR-20
01-APR-20 4 0 01-APR-20
01-MAI-20 6 0 01-MAI-20
01-JUN-20 2 0 01-JUN-20
01-JUL-20 4 0 01-JUL-20
01-AUG-20 7 2 03-AUG-20
01-SEP-20 3 0 01-SEP-20
01-OKT-20 5 0 01-OKT-20
01-NOV-20 1 1 02-NOV-20
01-DEZ-20 3 0 01-DEZ-20
12 rows selected.
I agree with all the other suggestions in this post that the original set of dates could and should have been reduced before doing my calculations, instead of fetching all calendar dates of the year. I just needed to understand what I could have done to fix my original code.
Thank you everyone for your contributions!
Upvotes: 1
Reputation: 35900
I think you can use following query:
with dates (dt) as
(select
add_months(trunc(sysdate,'year'),level-1)
from dual
connect by level <=12)
select dt, dt-dto as daysdiff from
(select case to_char(dt,'dy')
when 'sat'
then dt+2
when 'sun'
then dt+1
else dt
end as dt,
dto
from
(select case when extract(month from dt) = 1
then dt + 1
else dt
end as dt,
dt as dto
from dates))
See db<>fiddle demo.
Upvotes: 0
Related Questions
- How do I correctly compare dates using to_char?
- Difference in TO_CHAR format
- Oracle date format while using TO_CHAR()
- SQL Query to_char BETWEEN returns wrong results along with correct ones
- TO_CHAR from Date data type output difference
- to_char on a date field giving unexpected results
- TO_CHAR invalid number
- Oracle Date to_char Returns Different Results
- TO_CHAR only considering dates while comparing two time stamps?
- to_char and to_date are returning different output