last day of quarter [message #293834] |
Tue, 15 January 2008 03:54 |
buck444
Messages: 80 Registered: January 2006 Location: Luxembourg
|
Member |
|
|
Hi,
I would like to receive the last day of the quarter (DD-MON-YYYY).
My temp. Version looks like below:
SELECT CASE TO_CHAR (tradedate, 'YYYY-Q')
WHEN '2007-3'
THEN '30-sep-07'
WHEN '2007-4'
THEN '30-dec-07'
WHEN '2008-1'
THEN '31-mar-08'
WHEN '2008-2'
THEN '30-jun-08'
WHEN '2008-3'
THEN '30-sep-08'
WHEN '2008-4'
THEN '30-dec-08'
WHEN '2009-1'
THEN '31-mar-09'
WHEN '2009-2'
THEN '30-jun-09'
WHEN '2009-3'
THEN '30-sep-09'
WHEN '2009-4'
THEN '30-dec-09'
END as tradedate,
SUM (fee_euro)
FROM fundfees
WHERE tradedate >= '01-jul-07'
GROUP BY TO_CHAR (tradedate, 'YYYY-Q')
ORDER BY 1
Result:
TRADEDATE SUM(fee_EURO)
--------- -------------
30-dec-07 35010.84
30-sep-07 26204.15
31-mar-08 31268.29
This works of course, but there must be a nicer way instead of using hard coded date-values?!
[Updated on: Tue, 15 January 2008 03:55] Report message to a moderator
|
|
|
|
Re: last day of quarter [message #293881 is a reply to message #293834] |
Tue, 15 January 2008 07:46 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
buck444 wrote on Tue, 15 January 2008 04:54 |
FROM fundfees
WHERE tradedate >= '01-jul-07'
|
This is not how to use DATEs in Oracle. You are comparing a DATE column to a string. This is poor programming practice.
FOO SCOTT>l
1* select 1 from dual where sysdate > '01-jul-07'
FOO SCOTT>/
select 1 from dual where sysdate > '01-jul-07'
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
|
|
|
Re: last day of quarter [message #293893 is a reply to message #293840] |
Tue, 15 January 2008 08:49 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 15 January 2008 04:05 | Just arithmetic:
- truncate the date to the quarter
- add 3 months
- subtract one day
Regards
Michel
|
And to illustrate what Michel said.
select add_months(trunc(sysdate,'Q'),3) - 1 from dual;
ADD_MONTH
---------
31-MAR-08
|
|
|
|
|
Re: last day of quarter [message #294038 is a reply to message #293834] |
Wed, 16 January 2008 03:18 |
buck444
Messages: 80 Registered: January 2006 Location: Luxembourg
|
Member |
|
|
Bill,
when I saw your comment yesterday evening, it made sense not to compare to a string, but when I test it (sql*plus or TOAD), I get the below error message:
1 SELECT CASE TO_CHAR (tradedate, 'YYYY-Q')
2 WHEN '2007-3'
3 THEN '30-sep-07'
4 WHEN '2007-4'
5 THEN '30-dec-07'
6 WHEN '2008-1'
7 THEN '31-mar-08'
8 WHEN '2008-2'
9 THEN '30-jun-08'
10 WHEN '2008-3'
11 THEN '30-sep-08'
12 WHEN '2008-4'
13 THEN '30-dec-08'
14 WHEN '2009-1'
15 THEN '31-mar-09'
16 WHEN '2009-2'
17 THEN '30-jun-09'
18 WHEN '2009-3'
19 THEN '30-sep-09'
20 WHEN '2009-4'
21 THEN '30-dec-09'
22 END as tradedate,
23 SUM (fee_euro)
24 FROM fundfees
25 WHERE tradedate >= 01-jul-07
26 GROUP BY TO_CHAR (tradedate, 'YYYY-Q')
27* ORDER BY 1
WHERE tradedate >= 01-jul-07
*
ERROR at line 25:
ORA-00904: "JUL": invalid identifier
Any idea?
|
|
|
|
Re: last day of quarter [message #294046 is a reply to message #293834] |
Wed, 16 January 2008 04:26 |
buck444
Messages: 80 Registered: January 2006 Location: Luxembourg
|
Member |
|
|
Hi rajavu,
just to test the tip from Bill regarding the date...
Is this assumption correct?:
the correct way to compare to a date would be: TO_DATE('01-jul-07',dd-mon-yy)
But when I use just '01-jul-07' Oracle will make an implicit conversion from string to date.
|
|
|
|
|
|
Re: last day of quarter [message #294061 is a reply to message #294051] |
Wed, 16 January 2008 05:13 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
to_date('01-jul-07', 'dd-mon-yy') may be a date, but I very much doubt if it is the date you intend to use...
I think you should use rr instead of yy, or (FAR) better: use four digits for the year!
It's only been 8 years, and already people forgot?!
|
|
|
|
|
|
Re: last day of quarter [message #294117 is a reply to message #294096] |
Wed, 16 January 2008 09:10 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Littlefoot wrote on Wed, 16 January 2008 14:54 | Nothing to worry about, as the world will end in 2012 .
|
AHAA!!
That's exactly the same mistake.. 21-12-12 can be a lot of dates!
|
|
|