Wanna Date?
Dates are known to be exceedingly difficult and avoiding them at all costs is something of a skill. This function acts as a wrapper that calculates a date range before calling another procedure that also avoided using dates for its input date parameters. So despite it's 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. I left the comments in 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, except to make your application hard code the date format and language. So I decided to try this comparison. 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> alter session set nls_date_format = 'DD-MON-YYYY';
Session altered.
SQL> var d varchar2(11)
SQL> exec :d := '17-JUN-2006'
PL/SQL procedure successfully completed.
SQL> col start_date format a11
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 in a specific format and language. 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.