Home » SQL & PL/SQL » SQL & PL/SQL » Converting Date (Oracle 10g)
Converting Date [message #407620] Wed, 10 June 2009 14:55 Go to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
Using the following table as an example:

SHIFTTOTAL:

Name           Date                 Duration
Sam    01-FEB-09  7:30:00 AM         1200
Sam    01-FEB-09  9:30:00 PM         1500
Joe    02-FEB-09  7:30:00 AM         1000



I need to sum the Duration column by Name, Date.

This is easy:


select name, date, sum(duration)
from shifttotal
group by name, date



The issue is that I only want to group by the date portion of the Date column, I do not want to include the time portion

And I would like the output to always have midnight at the time portion of the date field and still be a date data type

thus the output should be

Sam, 01-FEB-09 12:00:00 am, 2700
Joe, 02-FEB-09 12:00:00 am, 1000

I have pulled my hair out trying to come up with a way to do this
Re: Converting Date [message #407622 is a reply to message #407620] Wed, 10 June 2009 15:09 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>The issue is that I only want to group by the date portion of the Date column, I do not want to include the time portion

So proceed to do so using the TO_CHAR() function which you should have been using in any case.
Re: Converting Date [message #407623 is a reply to message #407620] Wed, 10 June 2009 15:13 Go to previous messageGo to next message
klkuab
Messages: 25
Registered: November 2007
Junior Member
I can't use the to_char because that converts it to a character varchar data type. In the end I need it to be a date data type.
Re: Converting Date [message #407624 is a reply to message #407623] Wed, 10 June 2009 15:54 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Look at the TRUNC function
Re: Converting Date [message #407627 is a reply to message #407620] Wed, 10 June 2009 16:49 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>
klkuab wrote on Wed, 10 June 2009 13:13
I can't use the to_char because that converts it to a character varchar data type. In the end I need it to be a date data type.



A printed report only contains characters; so what is your point?
Re: Converting Date [message #407640 is a reply to message #407620] Wed, 10 June 2009 23:16 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Using the following table as an example:

Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: Duplicating two tables
Next Topic: Meaning of cursor.delete
Goto Forum:
  


Current Time: Wed Dec 07 20:41:53 CST 2016

Total time taken to generate the page: 0.41749 seconds