Last day in the year/ Quarter

[Duje]

Hi,

i'm looking for the way(best way ) to get last day in the current year and last day in current quarter.

for last year I created next SQL: SELECT TO_TIMESTAMP('31 Dec ' || TO_CHAR(my_date, 'YYYY') || ' 23:59:59', 'DD Mon YYYY HH:MI:SS') as dt_last_year_day FROM my_table.
where my_date is a column with list of dates.

for last quarter I cannot imagine any SQL.

could you please help in creating the 2nd and maybe in updating the first, if you know the better option to do it.

thank you for advice.

[id10t]

Hi!

Code: Select all

CREATE OR REPLACE FUNCTION current_quarter_last_day()
RETURN TIMESTAMP
  AS BEGIN
      RETURN CASE ((MONTH(SYSDATE()) - 1) // 3 + 1)
                  WHEN 1 THEN (YEAR(SYSDATE()) || '-03-31 23:59:59')::TIMESTAMP
                  WHEN 2 THEN (YEAR(SYSDATE()) || '-06-30 23:59:59')::TIMESTAMP
                  WHEN 3 THEN (YEAR(SYSDATE()) || '-09-30 23:59:59')::TIMESTAMP
                  WHEN 4 THEN (YEAR(SYSDATE()) || '-12-31 23:59:59')::TIMESTAMP
              END;
  END;

Code: Select all

db=> select current_quarter_last_day();
 current_quarter_last_day 
--------------------------
 2012-09-30 23:59:59

Code: Select all

db=> select  current_quarter_last_day() - '2012-09-03 21:01:01'::timestamp;
  ?column?   
-------------
 27 02:58:58
(1 row)