Home » SQL & PL/SQL » SQL & PL/SQL » last day of quarter
last day of quarter [message #293834] Tue, 15 January 2008 03:54 Go to next message
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 #293840 is a reply to message #293834] Tue, 15 January 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just arithmetic:
- truncate the date to the quarter
- add 3 months
- subtract one day

Regards
Michel
Re: last day of quarter [message #293881 is a reply to message #293834] Tue, 15 January 2008 07:46 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #293894 is a reply to message #293893] Tue, 15 January 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please, let OP build and post the query.

Regards
Michel
Re: last day of quarter [message #293964 is a reply to message #293834] Tue, 15 January 2008 15:20 Go to previous messageGo to next message
buck444
Messages: 80
Registered: January 2006
Location: Luxembourg
Member
thank you both for your answer and pointing to my error.

Re: last day of quarter [message #294038 is a reply to message #293834] Wed, 16 January 2008 03:18 Go to previous messageGo to next message
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 #294039 is a reply to message #293834] Wed, 16 January 2008 03:48 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:
tradedate >= 01-jul-07


01-jul-07                       is not a date ( Oracle assumes it as idenfier when it comes in SQL )
'01-jul-07'                     is not a date ( Oracle assumes it as string when it comes in SQL )
TO_DATE('01-jul-07',dd-mon-yy') is the date


And why are you trying out previous sql statement again ?

Thumbs Up
Rajuvan.

[Updated on: Wed, 16 January 2008 03:52]

Report message to a moderator

Re: last day of quarter [message #294046 is a reply to message #293834] Wed, 16 January 2008 04:26 Go to previous messageGo to next message
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 #294049 is a reply to message #294046] Wed, 16 January 2008 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps, sometimes, not always, so - you can't rely on it:
SQL> select count(*) from emp
  2  where hiredate < '01-jul-07';
where hiredate < '01-jul-07'
                 *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>
Re: last day of quarter [message #294051 is a reply to message #293834] Wed, 16 January 2008 04:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ie ,

Failed sql statement in Littlefoot's reply may work in different system. (It depends on System parameters )

SQL> select count(*) from emp
  2  where hiredate < '01-jul-07';

  COUNT(*)
----------
        14

SQL> select count(*) from emp
  2  where hiredate < '01-07-07';
where hiredate < '01-07--07'
                 *
ERROR at line 2:
ORA-01843: not a valid month


SQL>  select count(*) from emp
  2   where hiredate < 'jul-01-07';
 where hiredate < 'jul-01-07'
                  *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected


SQL>


Anyway , it is adviced to convert string to date using TO_DATE function on comparison with date identifier.

Thumbs Up
Rajuvan.

[Updated on: Wed, 16 January 2008 04:48]

Report message to a moderator

Re: last day of quarter [message #294059 is a reply to message #294051] Wed, 16 January 2008 05:08 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
rajavu1
Failed sql statement in Littlefoot's reply may work in different system.

That's why I said
LF
Perhaps, sometimes, not always, so - you can't rely on it
to the final agreement
rajavu1
it is adviced to convert string to date using TO_DATE function
Re: last day of quarter [message #294061 is a reply to message #294051] Wed, 16 January 2008 05:13 Go to previous messageGo to next message
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 #294082 is a reply to message #294061] Wed, 16 January 2008 06:35 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh well, none of the existing applications will have to worry about the Y3K bug Smile
Re: last day of quarter [message #294084 is a reply to message #294082] Wed, 16 January 2008 06:36 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Littlefoot wrote on Wed, 16 January 2008 13:35
Oh well, none of the existing applications will have to worry about the Y3K bug Smile


Laughing

But what with the year 2100 ?
Re: last day of quarter [message #294096 is a reply to message #294084] Wed, 16 January 2008 07:54 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Nothing to worry about, as the world will end in 2012 /forum/fa/1601/0/.
Re: last day of quarter [message #294117 is a reply to message #294096] Wed, 16 January 2008 09:10 Go to previous message
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 /forum/fa/1601/0/.

AHAA!!

That's exactly the same mistake.. 21-12-12 can be a lot of dates!
Previous Topic: Date format
Next Topic: Query Help!
Goto Forum:
  


Current Time: Sat Nov 09 17:57:07 CST 2024