Home » SQL & PL/SQL » SQL & PL/SQL » to_date format question
to_date format question [message #258987] Tue, 14 August 2007 01:13 Go to next message
aspectweaver
Messages: 3
Registered: July 2007
Junior Member
Hi,

I have a table that contains 2 columns which are date and paymentamount. I want to get the sum of payment amounts by month.
The command I tried is as follows:

select sum(amountpaid) from transactions where
operdate = to_date('jan-2007', 'mon-yyyy')

Using this command, I only get the sum(amountpaid) for the 1st of January 2007. Somehow because I don't specify the day component of the date, oracle defaults the day to the 1st. Is there any way around this? Thanks
Re: to_date format question [message #258992 is a reply to message #258987] Tue, 14 August 2007 01:25 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Try to Use Group by clause..

regards
Re: to_date format question [message #258993 is a reply to message #258987] Tue, 14 August 2007 01:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
select to_char(operdate, 'mm.yyyy'), sum(amountpaid)
from transactions
group by to_char(operdate, 'mm.yyyy');
Re: to_date format question [message #258996 is a reply to message #258987] Tue, 14 August 2007 01:29 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then you can use something like (not exhaustive list):
to_char(operdate,'MM/YYYY') = '01/2007'
trunc(operdate,'month') = to_date('jan-2007', 'mon-yyyy')
operdate >= to_date('01/01/2007','DD/MM/YYYY') and operdate < to_date('01/02/2007','DD/MM/YYYY')
...

Check which one is better.

Regards
Michel

[Edit: remove comment on date column name]

[Updated on: Tue, 14 August 2007 01:30]

Report message to a moderator

Previous Topic: ORA-01720 grant option does not exist
Next Topic: QUERY regarding procedure
Goto Forum:
  


Current Time: Fri Dec 09 04:14:22 CST 2016

Total time taken to generate the page: 0.07834 seconds