All Forums Database
Albright 8 posts Joined 07/11
20 Jul 2011
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)

Albright 8 posts Joined 07/11
20 Jul 2011

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));

ulrich 816 posts Joined 09/09
26 Jul 2011

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)

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.