Pages

Saturday, July 13, 2013

two date functions

More times I needed a function that returns specific date of current years or specific date of current month.

I wrote two functions that solve this request:

CREATE OR REPLACE FUNCTION public.this_month_day(integer)
 RETURNS date
 LANGUAGE sql
AS $function$
select (date_trunc('month', current_date) + ($1 - 1) * interval '1 day')::date
$function$;

CREATE OR REPLACE FUNCTION public.this_year_day(integer, integer)
 RETURNS date
 LANGUAGE sql
AS $function$
select (date_trunc('year', current_date) + ($1 - 1) * interval '1 month' + ($2-1) * interval '1day')::date
$function$;

postgres=# select this_year_day(7,15);
 this_year_day 
---------------
 2013-07-15
(1 row)

postgres=# select this_month_day(15);
 this_month_day 
----------------
 2013-07-15
(1 row)

No comments:

Post a Comment