| Date time in my sql [message #218519] |
Thu, 08 February 2007 09:23  |
helenkam
Messages: 3
Registered: February 2007
|
Junior Member |
|
|
I need to write sql in Oracle to count action codes breaking by month in date range let’s say between 01/01/2007 and 01/10/2007. But the problem for me is I have to count how many times we used the actions only between 7:00 PM and 9:00 PM every day in that date range. My question is : what I have to use in my where clause for date format in order to pull up only between 7:00 PM and 9:00 PM actions.
Please help.
Select to_char(a.action_date,'MM/YYYY') Action_Date, a.action, count(a.action)
From action a
Where ..............
Thanks in advance
Helen
|
|
|
|
| Re: Date time in my sql [message #218521 is a reply to message #218519] |
Thu, 08 February 2007 09:30  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
You could substract a truncated action_date form the real one and look if the result is between 19/24 and 21/24 :
.....
where action_date - trunc(action_date) between (19/24) and (21/24)
|
|
|
|
| Re: Date time in my sql [message #218527 is a reply to message #218521] |
Thu, 08 February 2007 09:47 |
helenkam
Messages: 3
Registered: February 2007
|
Junior Member |
|
|
Thank you for reply, but it is still not working for me , giving me error invalid number. Please see below, what I am doing wrong?
........
and to_number(to_char(trunc(a.action_date))) between (19/24) and (21/24)
and a.action_date>= to_date('2007/02/01','yyyy/mm/dd')
and a.action_date<= to_date('2007/02/05','yyyy/mm/dd')
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
