Dates are known to be exceedingly difficult and avoiding them at all costs is something of a skill. This function, in the spirit of Never do in SQL what you can do in PL/SQL, calculates a date range before calling another procedure that also avoided using dates for its input date parameters. So despite its absence, it is at least partially responsible for this mess.
I stripped the code down to its date handling which studiously avoids using date calculations wherever possible, and uses string handling instead, leaving in the comments because they are also the documentation.
create or replace function start_date ( p_range in varchar2, p_in_date in varchar2 -- DD-MON-YYYY format String ) return varchar2 as l_out_date varchar2(11); month varchar2(10) := to_char(to_date(p_in_date,'DD-MON-YYYY'),'MON'); year varchar2(10) := to_char(to_date(p_in_date,'DD-MON-YYYY'),'YYYY'); v_cnt_yr number; v_end_date date := to_date(p_in_date,'DD-MON-YYYY'); v_start_date varchar2(11); begin if p_range = 'QTD' THEN if month in ('JAN','FEB','MAR') then -- if given month = march and date is 31 -- then data for jan,feb and march. l_out_date := '01-JAN-'||year; elsif month in ('APR','MAY','JUN') then l_out_date := '01-APR-'||year; elsif month in ('JUL','AUG','SEP') then l_out_date := '01-JUL-'||year; elsif month in ('OCT','NOV','DEC') then l_out_date := '01-OCT-'||year; end if; elsif p_range = 'YTD' then -- beginning of the year. l_out_date := '01-JAN-'||year; elsif p_range = 'M' or p_range='MTD' then -- beginning of month l_out_date := '01-'||month||'-'||year; elsif p_range like 'B%' then v_cnt_yr := substr(ltrim(rtrim(p_range)),2); -- We take the start date as the first day after trailing -- back the required no. of months l_out_date := to_char(last_day(add_months( last_day(v_end_date), -v_cnt_yr)) + 1, 'DD-MON-YYYY'); end if; return l_out_date; end;
After reading it I thought, "So what does this do that TRUNC doesn't?" Apparently not a lot when you need to get the month, quarter or year to date. If you try this at home remember to format the return of START_DATE for readability since it usefully returns a 4000 character string.
SQL> exec :d := '17-JUN-2006' PL/SQL procedure successfully completed. SQL> select start_date('QTD',:d) start_date, 2 trunc(to_date(:d),'Q') from dual; START_DATE TRUNC(TO_DA ----------- ----------- 01-APR-2006 01-APR-2006 SQL> select start_date('YTD',:d) start_date, 2 trunc(to_date(:d),'Y') from dual; START_DATE TRUNC(TO_DA ----------- ----------- 01-JAN-2006 01-JAN-2006 SQL> select start_date('MTD',:d) start_date, 2 trunc(to_date(:d),'MM') from dual; START_DATE TRUNC(TO_DA ----------- ----------- 01-JUN-2006 01-JUN-2006
But what about the mysterious 'B%' format mask? This calculates the first day of the month, where 'Bn' is n-1 months ago, tricky eh? In SQL we are forced to call two functions instead of having START_DATE call ADD_MONTHS for us with two bonus LAST_DAYS thrown in for good measure. The n-1 bit could even be a bug but who knows?
SQL> select start_date('B12',:d) start_date, 2 trunc(add_months(to_date(:d),-11),'MM') from dual; START_DATE TRUNC(ADD_M ----------- ----------- 01-JUL-2005 01-JUL-2005
The convenience obviously outweighs the problem of having to deal with an undocumented date function that accepts and returns strings. Also with this function I have the luxury of substituting 'M' for 'MTD', but not 'Y' for 'YTD' or 'Q' for 'QTD' though I suspect these could be improvements for versions 2.0 and 3.0.