Cloud Photo

Azure Data Architect | DBA

Three Simple Date Functions

Over the years, I found that I was always typing the same lines of code to get at a very simple piece of information. At first, writing date functions seemed like a dumb idea. I mean, why would you need a function that returns the first day of a given month? The first of the month is always the first. I mean, the last_day(date) function makes sense. Some months have 30 days, some have 31, sometimes February has 28. It made sense to have the last_day() function. Here are three simple date functions that reduce writing the same three lines of code over and over into three letters.

Sometimes, you just don’t want to have to prepend the 01 and then format the string to get a date. This function is useful if you need to know if something happened between the first and last day of a month given a random date. For example, let’s say you need to run an ad hoc query to find the number of employees hired in the same month as Joe Worker. So, you know (or can find) the hiredate of Mr. Worker. We’ll stuff that data in the v_jwhd variable for the purposes of this example.

select count(1) as total_hired
from hr.employees
where hiredate between first_day(v_jwhd) and last_day(v_jwhd);

create or replace FUNCTION "FIRST_DAY" (v_day date)
return date is

Fiscal Year
Quick. What fiscal year is this date in? No. I’m not going to tell you the date. It could be any date. Again, rather than parsing and formatting some given date, I’d rather just run a function. This function was written to return a date (the first day of the fiscal yea, but you could easily write it to return just the numeric value for the year.

select fy(sysdate) from dual;

create or replace function fy(x_date IN date) return date as v_date date;
if to_char(x_date,'mm')>=10 then v_date := to_date('01-CT-'||to_char(x_date,'rrrr'),'dd-MON-rrrr');
elsif to_char(x_date,'mm')<10 then v_date := to_date('01-OCT-'||(to_char(x_date,'rrrr')-1),'dd-MON-rrrr');
end if;
return v_date;

Week of Quarter
I don’t know about you, but where I work, there are often programs that run quarterly and progress is often reported weekly. Of course, given a date, you can easily find out which quarter the date is in (to_char(date,’Q’)) or week of the year (to_char(date,’ww’)) or even week of the month (to_char(date,’w’)). But what about our progress during the third week of the second quarter? Enter my WOQ (week of quarter) function… Give it a date and it will tell you what week of the quarter for that date.

select sum(sales) as total_sales, woq(sale_date) from sales.receipts group by woq(sale_date);

create or replace function WOQ (v_date date) return number as v_w number;
select 1+(to_char(to_date(v_date),'WW')-to_char(to_date(decode(to_char(to_date(v_date),'Q'),1,'01-JAN',2,'01-APR',3,'01-JUL',4,'01-OCT')||to_char(v_date,'rrrr')),'WW')) into v_w from dual;
return v_w;

Well, there you have it. There are probably better ways of getting the same results, but these work for me. Besides, they’re functions. Run them once and they’re compiled and stored in memory so there’s no need to reparse and recompile the same lines of code over and over for different statements.

Leave a Reply