Complex Date Logic

Database
Enthusiast

Complex Date Logic

I've been searching the forums for cool data logic, but haven't found exactly what I'm looking for.
I need to do the following 2 things (in different SQL's).

(1) Find the PREVIOUS Oct-1. So if it is Oct-1 or later, I want this year. If it is Jan-1 through Sep-30, I want last year. I'll be using this in a WHERE clause

AND a.process_date > xxxx (xxxx is the previous Oct-1)

I could always use a CASE statement to extract the current month and have 2 different selects depending on if it is Jan-Sep or Oct-Dec.

(2) Find all data from the previous month. So if I'm runnig the SQL in the month of July 2011 (doesn't matter what day) then I would get everything between and including Jun-1, 2011 and Jun-30, 2011. This would also be used in a WHERE clause

AND a.process_date BEWTEEN xxxx AND yyyy (xxxx is the 1st day of last month and yyyy is the last day of last month)
2 REPLIES
Enthusiast

Re: Complex Date Logic

OK, had some typos, this should work. I could still use help on my original problem #1.

SELECT ............
FROM ......... a
WHERE a.process_date BETWEEN (ADD_MONTHS((DATE - DAYOFMONTH(DATE))+1,-1))
AND (DATE - DAYOFMONTH(DATE));
Supporter

Re: Complex Date Logic

try

select calendar_date,
case when cast(extract(year from current_date) !! '-10-01' as date)-calendar_date > 0 then cast((extract(year from current_date)-1) !! '-10-01' as date) else cast(extract(year from current_date) !! '-10-01' as date) end
from sys_calendar.calendar
where current_date = calendar_date
or
calendar_date = add_months(current_date, 4)