Month Function???

[pcawdron]

Is there a Month() function in DB2 SQL?<br><br>I need to tell how many months have elapsed between a certain date and now, i.e. between 27/11/98 and CURDATE()<br><br>Currently I'm using... (DAYS(CURDATE())-DAYS(START_DATE))/30<br><br>But the division slows the query down and leaves me with a nasty decimal running out to 0.000000000.&nbsp;&nbsp;With no ROUND() function in DB2 SQL, I'm left with a messy solution.<br><br>Any iadeas?<br>

 

[ratou]

The following is an example you can follow<br>SQL QUERY run<br>&nbsp;on 2000-07-17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>SELECT EMPL_NUM, ACCT_NO, ACCT_SEQ_CODE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, HIST_EFF_DATE, MONTH(CURRENT DATE) - MONTH(HIST_EFF_DATE)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>FROM db2.table<br>WHERE ACCT_NO = 9867<br>AND HIST_EFF_DATE BETWEEN '1999-12-31' AND CURRENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br><br>nunber&nbsp;&nbsp;&nbsp;&nbsp;acct&nbsp;&nbsp;seq&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;hist-eff&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;# of months<br>&nbsp;1516&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9867&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2000-01-16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<br>&nbsp;2052&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9867&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2000-04-11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3 <br>&nbsp;4892&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9867&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2000-02-28&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5<br>&nbsp;3009&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9867&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2000-01-08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6 <br>&nbsp;5244&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;9867&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2000-04-04&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>

 

[pcawdron]

Thanks,<br><br>That'll work within the confines of the same year but produces -1 for the difference between August 1999 and July 2000.&nbsp;&nbsp;So far I've constructed...<br><br>CASE WHEN <br>((DAYS(CURDATE())- DAYS(PREVIOUS_DATE) )/30) - INTEGER((DAYS(CURDATE())-<br>DAYS(PREVIOUS_DATE) )/30) &gt;= 0.5 <br>THEN <br>INTEGER((DAYS(CURDATE())-<br>DAYS(PREVIOUS_DATE) )/30)+1<br>ELSE <br>INTEGER((DAYS(CURDATE())-<br>DAYS(PREVIOUS_DATE) )/30)<br>END<br>MONTHS_ELAPSED<br><br>This seems to be an effective work-around although I'd prefer a simplier solution.

 

[ratou]

pcawdron, here is another way. I think it's a good way to do it.<br><br>SELECT CLAIM_NUM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, CLAIM_EFF_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, CURRENT DATE - CLAIM_EFF_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, YEAR(CURRENT DATE - CLAIM_EFF_DATE)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, MONTH(CURRENT DATE - CLAIM_EFF_DATE)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, DAY(CURRENT DATE - CLAIM_EFF_DATE)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;, (YEAR(CURRENT DATE - CLAIM_EFF_DATE) * 12)&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;+ MONTH(CURRENT DATE - CLAIM_EFF_DATE)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>FROM DB2.TABLE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>WHERE AREA_NO = 999 AND&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;CLAIM_EFF_DATE BETWEEN '1950-01-01' AND CURRENT DATE<br>ORDER BY 3<br><br>Query result run on 2000-07-19<br><br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CLAIM<br>CLAIM&nbsp;&nbsp;EFF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATE<br>&nbsp;NUM&nbsp;&nbsp;&nbsp;DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DURATION&nbsp;&nbsp;&nbsp;YEARS MONTHS&nbsp;&nbsp;DAYS&nbsp;&nbsp;# OF MON<br>------&nbsp;&nbsp;---------- ----------- -----&nbsp;&nbsp;------ -----&nbsp;&nbsp;-----------<br>&nbsp;65401&nbsp;&nbsp;2000-06-30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br>&nbsp;&nbsp;1344&nbsp;&nbsp;2000-06-28&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br>123415&nbsp;&nbsp;1999-12-30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;620&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6<br>&nbsp;25753&nbsp;&nbsp;1999-08-02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1117&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11&nbsp;&nbsp;17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11<br>&nbsp;&nbsp;3616&nbsp;&nbsp;1999-07-19&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12<br>&nbsp;&nbsp;5575&nbsp;&nbsp;1986-11-17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;130802&nbsp;&nbsp;&nbsp;&nbsp;13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;8&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;164<br>&nbsp;14181&nbsp;&nbsp;1984-03-04&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;160415&nbsp;&nbsp;&nbsp;&nbsp;16&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;196<br><br>&nbsp;<br>I hope this makes it easier for you.&nbsp;&nbsp;Let me know<br>

 

[pcawdron]

Dear Ratuo,<br><br>Excellent, thanks.&nbsp;&nbsp;That's exactly what I was looking for. I knew there had to be a simpler way of doing this.&nbsp;&nbsp;Your solution works very well.<br><br>Thanks again for taking the time to help,<br><br>Regards,<br><br>Peter<br>

 

[chidi]

It seems like I send a faq everyday but do not see appear on the forum.
Let me try again.
I need to know if someone can tell me how to write a quarter function.
Example, I have 2 tables, t1 and t2.
t1 has 3 columns - month_no as char, year_no as char
and a key called calendar_id.
t2 has two colums - amount and key calendar_id.
What I am trying to do is to determine the quarterly amount for each year.
I need my output to look like this:
period amount
1q98 200
2q98 300
3q98 0
4q01 50.
Like I said, it could be any year and any quarter.
I just the amount displayed for each quarter of a given year.
Can anyone please help me?
chidi