Syntax for Last day of last Quarter

[americanmc]

Hi,
I have a need to create a syntax , so that basing on the current_date, the sql generated will always give me the Last day of the Last Quarter.
I am new to SQL, so need help
thanks.
Ed

[Rabbit]

Define quarter. Calendar quarter? Fiscal Quarter? If fiscal, what is the start and end? It varies.

But the basic algorithm is this:

if (month of some date) <= (month of last date in fiscal year) then
   use current year
else
   use next year
end if

[americanmc]

We need the calendar quarter. So if I run the SQL now, it should give me sep 30 2012. and if I run it in Jan 2013, then it should give me 31 Dec 2012.

[Rabbit]

Use the date_trunc() function to get the first day of the quarter and then subtract one day.

[americanmc]

the date_trunc() function does not work in Postgres. Can u send me the correct SQL for last day of Quarter.

[Rabbit]

Sure it does. Here is PostgreSQL's list of date/time functions. You will see that date_trunc is on there.

http://www.postgresql.org/docs/8.3/static/functions-datetime.html

[americanmc]

yes, it does, i apprecaite. I am getting first day of curretn quarter, still looking for how to minus one day:
select date_trunc('qua rter',current_d ate)

[americanmc]

Once again I apprecaite the help this forum is providing.

[Rabbit]

In that same link, it also shows you how to subtract a time interval.

[americanmc]

Yes, Thanks Rabbit, it works..

select date(date_trunc ('quarter',curr ent_date))-integer '1'