Nightmarish Date Calculation

Quick background: My employer uses a rather odd (to others, it seems) date range for our Fiscal months - the 22nd to the 21st of each month. For example, Fiscal February runs 01.22.2012 to 02.21.2012.

Up until this point I’ve been able to use SYSDATE and simple functions to calculate dates in my reporting - they’ve just been the previous day, or previous 7 days, etc.

Now I need to work on reports that have this dynamic date range, and I’m not sure where to start.

What I’m looking for is something that will look at today’s date (03.02.2012, for example), and logically determine that the current fiscal month (and thus date range for the report) is 02.22.2012 - 03.21.2012. One of the nasty caveats to this is that on 03.22.2012, thereport range needs to stay 02.22.2012 - 03.21.2012, and it’s not until the 23rd that it would move on to the next fiscal month.

Is this even possible?

There is probably a better way to do this and I am not 100% sure it works the way you want but…

ReportDate between to_date(concat(to_char(add_months(sysdate,-1),‘MM’), concat(‘22’,to_char(add_months(sysdate,-1),‘YYYY’))),‘MM-DD-YYYY’) and to_date(concat(to_char(sysdate,‘MM’), concat(‘22’,to_char(sysdate,‘YYYY’))),‘MM-DD-YYYY’)

I assumed that on the 22nd of the month you still needed the previous fiscal month

Are you using Oracle? If so try this.

case

when to_number (to_char (sysdate, 'DD')) < 23
then

to_date (to_char (trunc (add_months (sysdate, -1), 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
else
to_date (to_char (trunc (sysdate, 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
end as begin_date_range,

case

when to_number (to_char (sysdate, 'DD')) < 23
then

to_date (to_char (trunc (sysdate, 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
else
to_date (to_char (trunc (add_months (sysdate, 1), 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
end as end_date_range

N.B., If you use Dave - Stt’s formula

for a sysdate of 2012-03-22 you get
begin date=2012-02-22
end date=2012-03-22

for a sysdate of 2012-03-23 you also get
begin date=2012-02-22
end date=2012-03-22

If you use the formula in my previous post

for a sysdate of 2012-03-22 you get
begin date=2012-02-22
end date=2012-03-21

for a sysdate of 2012-03-23 you get
begin date=2012-03-22
end date=2012-04-21

I’m not sure which one is the right one for you, but I believe it’s the second one.

Message was edited by: jacquesrk

You are correct sir, your 2nd example is exactly what I need to have happen. My question is this, will it still work correctly on months with different lengths, or across a year? For example, Fiscal January '12 was 12.22.2011 - 01.21.2012.

Now, for the fun part (and it may be because it's a Friday and my brain is a bit tired, but...), how do I integrate a CASE statement into a WHERE clause? This is the actual date select code as it stands currently:

AND W.WFINDATE BETWEEN DATE '2012-02-22' AND DATE '2012-02-29'

And do I need to wrap the output in a to_char in order to put it in the format above?

jacquesrk wrote:
N.B., If you use Dave - Stt's formula

for a sysdate of 2012-03-22 you get
begin date=2012-02-22
end date=2012-03-22

for a sysdate of 2012-03-23 you also get
begin date=2012-02-22
end date=2012-03-22

If you use the formula in my previous post

for a sysdate of 2012-03-22 you get
begin date=2012-02-22
end date=2012-03-21

for a sysdate of 2012-03-23 you get
begin date=2012-03-22
end date=2012-04-21

I'm not sure which one is the right one for you, but I believe it's the second one.

Message was edited by: jacquesrk

Looks like you guys have this well in hand. But I did want to mention that you can define a custom calendar to work with the date range literals.

Debbie

jacquesrk - you're awesome. I've thrown every test case at your solution that I can think of (replacing SYSDATE with DATE 'YYYY-MM-DD' to simulate dates) and I haven't been able to break it. This problem has been vexing me for well over two years.

Debbie - I was never quite able to figure out how to integrate that into my code, and to make sure that it rolled forwarded as I needed it to. That, and I really need most of my solutions to be client neutral in the horrible event we decide to forgo our enterprise license at some point.

Also, I did determine how to work this into my WHERE clause as a sub-SELECT in case anyone else needs to see it:

SELECT ACCTCORP, WJOB, COUNT(*)
FROM IDST_WIP
WHERE WFINDATE BETWEEN (SELECT CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < 23
THEN TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
ELSE TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
END AS FISCALSTART
FROM DUAL)
AND (SELECT CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < 23
THEN TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
ELSE TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
END AS FISCALEND
FROM DUAL)
AND ACCTCORP = 09579
GROUP BY ACCTCORP, WJOB

Again, a BIG Thank You to everyone! :slight_smile:

Hello N.B., to answer your questions:
as your tests show, the expression will return the desired result on months with different lengths or across a year (that’s because we use the Oracle ADD_MONTHS function, which will take care of those details for us)

to use it in a where clause, you can skip the “select … from dual” and put the expression directly in the where clause, as long as you remember to remove the column alias.
Meaning, my expression said CASE … END AS column_alias
remove the AS column_alias and you can put the expression in a where clause, like this:

WHERE WFINDATE BETWEEN (CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, ‘DD’)) < 23
THEN TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), ‘MM’), ‘YYYYMM’) || ‘22’, ‘YYYYMMDD’)
ELSE TO_DATE(TO_CHAR(TRUNC(SYSDATE, ‘MM’), ‘YYYYMM’) || ‘22’, ‘YYYYMMDD’)
END)
AND (CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, ‘DD’)) < 23
THEN TO_DATE(TO_CHAR(TRUNC(SYSDATE, ‘MM’), ‘YYYYMM’) || ‘21’, ‘YYYYMMDD’)
ELSE TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), ‘MM’), ‘YYYYMM’) || ‘21’, ‘YYYYMMDD’)
END)

Forgot your third question about putting the CASE … END expression inside a TO_CHAR: don’t do that if WFINDATE is a column of DATE datatype. You want to compare like datatypes as much as possible. If WFINDATE is a DATE column then you should use the CASE … END expression as is, since that expression returns a DATE value.