By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Syntax for Last day of last Quarter

P: 6
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
Nov 29 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,347
Define quarter. Calendar quarter? Fiscal Quarter? If fiscal, what is the start and end? It varies.

But the basic algorithm is this:
Expand|Select|Wrap|Line Numbers
  1. if (month of some date) <= (month of last date in fiscal year) then
  2.    use current year
  3. else
  4.    use next year
  5. end if
Nov 29 '12 #2

P: 6
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.
Nov 29 '12 #3

Rabbit
Expert Mod 10K+
P: 12,347
Use the date_trunc() function to get the first day of the quarter and then subtract one day.
Nov 29 '12 #4

P: 6
the date_trunc() function does not work in Postgres. Can u send me the correct SQL for last day of Quarter.
Nov 29 '12 #5

Rabbit
Expert Mod 10K+
P: 12,347
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/s...-datetime.html
Nov 29 '12 #6

P: 6
yes, it does, i apprecaite. I am getting first day of curretn quarter, still looking for how to minus one day:
select date_trunc('quarter',current_date)
Nov 29 '12 #7

P: 6
Once again I apprecaite the help this forum is providing.
Nov 29 '12 #8

Rabbit
Expert Mod 10K+
P: 12,347
In that same link, it also shows you how to subtract a time interval.
Nov 29 '12 #9

P: 6
Yes, Thanks Rabbit, it works..

select date(date_trunc('quarter',current_date))-integer '1'
Nov 30 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.