Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Anniversary Dates Between Two Periods

Hi all,
I am having issues with a query to show anniversaries between two dates. I have the below query to show the anniversary date:
SELECT
add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('&TO_DATE','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) as anniversary_date
FROM Table_A
WHERE
add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('31/03/2022','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) BETWEEN TO_DATE('01/04/2021','DD/MM/YYY') AND TO_DATE('31/03/2022','DD/MM/YYYY')
I would like to run the report between two dates but when i add the above statement in the WHERE clause it is only showing results for the year 2022 and not the From Date (2021).
Any help is appreciated!
TIA
Answers
-
but when i add the above statement
ORA-00942: table or view does not exist
-
You wont have access to the table as its on my server. You can recreate a table on your own server and test it out
-
Hi @User_OK580 ,
I think there is a logical error in your statement: You're extracting the whole year from the end date, sustract the whole year of the start date from it and multiply the result by 12. This is the number of months you the add to the start date. That's why you only get results for the year wich the end date falls into.
A more flexible solution wich meets your requirements would be the following:
WITH anniversaries (ident, anniversary_date, lower_bound, upper_bound) AS (SELECT ident, start_date AS anniversary_date, TO_DATE ('[Lower Bound]', 'DD.MM.YYYY') AS lower_bound, TO_DATE ('[Upper Bound]', 'DD.MM.YYYY') AS upper_bound FROM table_a UNION ALL SELECT anniversaries.ident, ADD_MONTHS (anniversaries.anniversary_date, 12) AS anniversary_date, anniversaries.lower_bound, anniversaries.upper_bound FROM anniversaries WHERE anniversaries.anniversary_date < anniversaries.upper_bound) SELECT anniversaries.ident, MIN (anniversaries.anniversary_date) AS anniversary_date FROM anniversaries WHERE anniversaries.anniversary_date BETWEEN anniversaries.lower_bound AND anniversaries.upper_bound GROUP BY anniversaries.ident;
The ident stands for an identifier in the table_a. Lower_bound and upper_bound are the dates you define for the start and the end of the period you want to watch the anniversaries. If the period defined is longer than one year it is possible, then more than one anniversary per ident falls into it. Thats why I take the least using min().
Best regards
Jan
-
HI, @User_OK580
You wont have access to the table as its on my server. You can recreate a table on your own server and test it out
Right; to enable people to recreate a table, post CREATE TABLE and INSERT statements for a little sample data, and the exact results you want from the given data. Include examples of any special cases you need to handle (e.g., a row with no anniversaries in the target window.)
Always say which version of Oracle you're using (e.g. 12.2.0.1.0).
-
Oracle month logic isn't always intuitive. If you add months to the last day of the month it will return last day of the resulting month. So assume person was born Feb 28 of a non-leap year, say Feb 28, 2001. Look what ADD_MONTHS will return every leap year:
SQL> alter session set nls_date_format='FMDD Month, YYYY'; Session altered. SQL> select add_months(date '2001-02-28',36 + 48 * (level - 1)) from dual connect by level <= 5; ADD_MONTHS(DATE'20 ------------------ 29 February, 2004 29 February, 2008 29 February, 2012 29 February, 2016 29 February, 2020 SQL>
As you can see ADD_MONTHS will not return correct birthday for such person.
SY.
-
Hi @Solomon Yakobson ,
you are right. Thank you very much for your tip. Thanks god this is the only special case which has to be handled. I've done so in the following query. For someone who was born on 29.02. of a leap year this solutions returns the 28.02. for non leap years and 29.02. for leap years:
WITH anniversaries (ident, date_of_birth, anniversary_date, lower_bound, upper_bound) AS (SELECT ident, start_date AS date_of_birth, start_date AS anniversary_date, TO_DATE ('[Lower Bound]', 'DD.MM.YYYY') AS lower_bound, TO_DATE ('[Upper Bound]', 'DD.MM.YYYY') AS upper_bound FROM table_a UNION ALL SELECT anniversaries.ident, anniversaries.date_of_birth, CASE WHEN TO_CHAR (anniversaries.date_of_birth, 'DD.MM.') = '28.02.' AND TO_CHAR ( ADD_MONTHS (anniversaries.anniversary_date, 12), 'DD.MM.') = '29.02.' THEN ADD_MONTHS (anniversaries.anniversary_date, 12) - 1 ELSE ADD_MONTHS (anniversaries.anniversary_date, 12) END AS anniversary_date, anniversaries.lower_bound, anniversaries.upper_bound FROM anniversaries WHERE anniversaries.anniversary_date < anniversaries.upper_bound) SELECT anniversaries.ident, MIN (anniversaries.anniversary_date) AS anniversary_date FROM anniversaries WHERE anniversaries.anniversary_date BETWEEN anniversaries.lower_bound AND anniversaries.upper_bound GROUP BY anniversaries.ident;
Best regards
Jan
-
Hi, @Jan Gorkow
Thanks god this is the only special case which has to be handled.
Actually, there's another special case: October, 1582, at the change from Julian to Gregorian calendars. (I'll bet OP isn't concerned about that.) If "aniversaries" are not exactly 1 year apart, then there will be lots of special cases. For example, if reviews are done every 3 months, and start_date is November 28, 2021; the first review is done on February 28, 2022, but the second review should be on May 28, 2022 (6 months after start_date), not May 31, 2022 (3 "months" after the previous review_date, as ADD_MONTHS reckons it).
To deal with that problem, you can modify your query like this:
WITH anniversaries (ident, date_of_birth, anniversary_date, next_anniversary_num, lower_bound, upper_bound) AS (SELECT ident, start_date AS date_of_birth, start_date AS anniversary_date, 1 AS next_anniversary_num TO_DATE ('[Lower Bound]', 'DD.MM.YYYY') AS lower_bound, TO_DATE ('[Upper Bound]', 'DD.MM.YYYY') AS upper_bound FROM table_a UNION ALL SELECT ident, date_of_birth, ADD_MONTHS (date_of_birth, 12 * next_anniversary_num) AS anniversary_date, next_anniversary_num + 1 AS next_anniversry_num lower_bound, upper_bound FROM anniversaries WHERE anniversary_date < upper_bound -- or lower_bound, slightly faster ) SELECT ident, MIN (anniversary_date) AS anniversary_date FROM anniversaries WHERE anniversary_date BETWEEN lower_bound AND upper_bound GROUP BY ident;
I'll test this when OP posts some sample data.
Actually, I'd do it this way even for true 12-month anniversaries, since it avoids that CASE expression. Of course, the hard-coded 12 needs to be changed to use periods of a different length,
-
As an aside, please learn to format your code. It makes it much easier to read and comprehend. Here's what your code looks like when following the procedure in the link.
SELECT add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('&TO_DATE','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) as anniversary_date FROM Table_A WHERE add_months(start_date,(EXTRACT(YEAR FROM TO_DATE('31/03/2022','DD/MM/YYYY')) - EXTRACT(YEAR FROM start_date))*12) BETWEEN TO_DATE('01/04/2021','DD/MM/YYY') AND TO_DATE('31/03/2022','DD/MM/YYYY')
-
this is the only special case which has to be handled. I've done so in the following query. For someone who was born on 29.02. of a leap year this solutions returns the 28.02. for non leap years and 29.02. for leap years
So, of course, 28 February is not "the only special case which has to be handled" - you immediately go on to state the other "special case", which is 29 February.
Question back to the OP: How should that be handled? In some locales, the official anniversary for 29 February in a non-leap year is 28 February, but in other locales it is 1 March. Only the OP (or, perhaps, his business user) can answer that question.
-
Hi all,
Thanks for your support in this. In the future i will post any questions as per the format intended to answer the question successfully.
@mathguy if the anniversary date is 29/02 and its a non leap year then the anniversary will be a day behind, 28/02.
The only anniversaries we want to report on are 10,25,40 and 50 years. We would like to run the report between two dates which will show any employees who have been in the business for 10,25,40 and 50 years when running the report.