Once gotcha to consider when using BETWEEN, which is inclusive, I think (mine is). So this translates to something like "where birthday <= 08/01/2004 and birthday <= 09/01/2004", for example. So, 9/1 will appear in both Aug and Sept version of the report.
Original Message:
Sent: 07-16-2025 03:06 PM
From: Scott Douglas
Subject: How to account for months with more/less than 30 days using ADD_MONTHS function
As I test these options out, I am finding a small issue with Bob's SQL as well when run in our Oracle database. When I use MONTHS_BETWEEN() with today's date and the birthdate, the number returned is a decimal. Our target is 252 months, but if you run the SQL on 7/1/2025, only 7/1/2004 will come out to exactly 252 using MONTHS_BETWEEN(). 7/2/2004 will return ~ 251.97 months. When you wrap that with TRUNC(), that turns into 251, which is less than 21 when divided by 12.
A quick fix to this, at least in our database, is to use CEIL() instead of TRUNC(). This, when run on the first of the month, will take all the fractional MONTHS_BETWEEN values and round them up to the nearest integer. For instance, when run on 7/1/2025, you would get:
Birthdate CEIL(MONTHS_BETWEEN(SYSDATE, TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD')))---------- ---06/30/2004 25107/01/2004 25207/02/2004 25207/31/2004 25208/01/2004 253
Here's that expression to get years instead of months:
CEIL(MONTHS_BETWEEN(SYSDATE, TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD') ))/12
Also, it appears that Scott Nishizaki's suggestion would work, perhaps with less modification to the original user query:
AND A.BIRTHDATE BETWEEN ADD_MONTHS(SYSDATE, -12*21) AND ADD_MONTHS(SYSDATE, -12*21 + 1)
------------------------------
Scott Douglas
Sr. Business Systems Analyst, Student Information Systems
Azusa Pacific University
Original Message:
Sent: 07-15-2025 01:46 PM
From: Scott Nishizaki
Subject: How to account for months with more/less than 30 days using ADD_MONTHS function
I think you can get away with continuing with your current criteria set up as pictured above, but instead of using the +30 outside of add months, put and + 1 inside of it? So the 2nd expression would become
ADD_MONTHS(SYSDATE, -12*21 + 1)
------------------------------
Scott Nishizaki
Developer/Analyst
Azusa Pacific University
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
Original Message:
Sent: 07-14-2025 05:49 PM
From: Richard Pope
Subject: How to account for months with more/less than 30 days using ADD_MONTHS function
Hi everyone!
It is very possible that I am overthinking this one, but I am writing a query for a user that wants to see all active students who will be turning 21 years old within the next month, and they want the query to be scheduled to run on the 1st of every month.
In its current configuration, it will pull in students with birthdays coming up within the next 30 days (excluding those with birthdays on the date of the run), but is there a better way to do this that will account for months with less than 30 days (February, including leap years) and those with more (July, August, etc.)?
Since the report is going to be used as an email distribution list, it will duplicate some students (those with birthdays on March 2 would appear on both the February and March reports), and based on my testing, if it runs on the 1st of every month, it will miss anyone with a birthday on the 1st or 31st of the month.
Current criteria:
Thank you in advance!
Richard
------------------------------
Richard Pope
Senior Assistant Registrar for CU-SIS Development
University of Colorado Colorado Springs
------------------------------
Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------