Majda Majda - 1 year ago 77
Java Question

How to have the four quarter of Year relative to a date

I'm working on java swing application with hibernate .
I want to show on jasperreport results for each quarter of a year (I have in database dates so I will extract years that correspond to these dates that I have on my database) I find some solutions but I think that's not really a good one ,that's the easy way:

select * from dbcheques.Cheque
where YEAR(dateEncaissement) = '2013'
and (MONTH(dateEncaissement) = 7
or MONTH(dateEncaissement) = 8 or MONTH(dateEncaissement) >= 9)

that will return the third quarter of that year , nearly the same query for other quarter only the number change:

For the first quarter

and (MONTH(dateEncaissement) = 1 or MONTH(dateEncaissement) = 2
or MONTH(dateEncaissement) >= 3)

For the second quarter

and (MONTH(dateEncaissement) = 4 or MONTH(dateEncaissement) = 5
or MONTH(dateEncaissement) >= 6)

For the last quarter

and (MONTH(dateEncaissement) = 10 or MONTH(dateEncaissement) = 11
or MONTH(dateEncaissement) = 12)

this is the interface:


Answer Source

If your question is "how to get the values for a given quarter (1, 2, 3 or 4)", then you can simply use

where ((MONTH(dateEncaissement) - 1) DIV 3) + 1 = <quarter>

If you want to select rows from the table, including the quarter of each row, then use the same trick in the select clause:

select *, (((MONTH(dateEncaissement) - 1) DIV 3) + 1) as quarter from ...
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download