Home » SQL & PL/SQL » SQL & PL/SQL » Date time in my sql
Date time in my sql [message #218519] Thu, 08 February 2007 09:23 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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')
icon14.gif  Re: Date time in my sql [message #218529 is a reply to message #218521] Thu, 08 February 2007 09:56 Go to previous messageGo to next message
helenkam
Messages: 3
Registered: February 2007
Junior Member
THANK yOU!!!!!
I fixed to :

and ci.RECEIVED-trunc(ci.RECEIVED) between (19/24) and (21/24)
and ci.RECEIVED >= to_date('2007/02/01','yyyy/mm/dd')
and ci.RECEIVED <= to_date('2007/02/05','yyyy/mm/dd')


AND IT WORKS!!!!!!!!!!!!!!!1


THANK YOU!!!!!!!!!!!!!


Re: Date time in my sql [message #218555 is a reply to message #218529] Thu, 08 February 2007 11:23 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Your welcome Wink
Previous Topic: Truncate date string to mm/yyyy
Next Topic: Truncate date string to mm/yyyy
Goto Forum:
  


Current Time: Thu Dec 05 12:32:06 CST 2024