Home » SQL & PL/SQL » SQL & PL/SQL » Set time parameter in date/time field (oracle 9i)
Set time parameter in date/time field [message #435667] Thu, 17 December 2009 05:16 Go to next message
davea300
Messages: 2
Registered: December 2009
Junior Member
I'm trying to set a time parameter using SQL within a business objects report. I want to pull out all records with a time stamp after 5pm and before 8am. Problem is the field is a date/time field and i can't figure out how to ignore the date part. so far I have:

and a.jcompdate > to_date('17:00', 'HH24:MI')
and a.jcompdate < to_date('08:00', 'HH24:MI')

which doens't return any records.

the field's format is DD-MON-YY HH:MI and I'm only concerned about the time for this query.

Re: Set time parameter in date/time field [message #435668 is a reply to message #435667] Thu, 17 December 2009 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do the opposite:
TO_CHAR(jcompdate,'HH24:MI') between '08:00' and '17:00'
or the like

Regards
Michel
Re: Set time parameter in date/time field [message #435669 is a reply to message #435667] Thu, 17 December 2009 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
or better
extract(hour from jcompdate) between 8 and 17

Regards
Michel
Re: Set time parameter in date/time field [message #435671 is a reply to message #435669] Thu, 17 December 2009 05:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Bear in mind that if you want to use an index on this part of the cuqery, then you'll need to create a function based index.
Re: Set time parameter in date/time field [message #435672 is a reply to message #435667] Thu, 17 December 2009 05:33 Go to previous messageGo to next message
jaganerp@gmail.com
Messages: 63
Registered: April 2008
Member
Hi Dave,

you are picking the data based on time right,let me the jcompdate is what type of data type,is it time stamp or date,if data type is timestamp then you can pass time by using like,but hear you will face problem,i mean you don't know on what day and at what time,so you have to pass the parameter value with date and time.

Regards
jagan

Re: Set time parameter in date/time field [message #435676 is a reply to message #435672] Thu, 17 December 2009 05:39 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think data type of the column is DATE

Quote:
Problem is the field is a date/time field


and OP has got the solution

regards,
Delna
Re: Set time parameter in date/time field [message #435681 is a reply to message #435668] Thu, 17 December 2009 06:13 Go to previous messageGo to next message
davea300
Messages: 2
Registered: December 2009
Junior Member
Michel Cadot wrote on Thu, 17 December 2009 05:20
Do the opposite:
TO_CHAR(jcompdate,'HH24:MI') between '08:00' and '17:00'
or the like

Regards
Michel


Excellent got it working now thanks, ended up using:

to_number(to_char(jcompdate,'HH24')) between 17 and 24
or to_number(to_char(jcompdate,'HH24')) between 24 and 8

Thanks for your help

[Updated on: Thu, 17 December 2009 06:14]

Report message to a moderator

Re: Set time parameter in date/time field [message #435683 is a reply to message #435681] Thu, 17 December 2009 06:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case it is better to use the EXTRACT function as in my second post.

Regards
Michel
Re: Set time parameter in date/time field [message #435689 is a reply to message #435681] Thu, 17 December 2009 06:24 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could do this in a single statement as:
to_number(to_char(jcompdate,'HH24')) NOT BETWEEN 8 and 17 
Previous Topic: Time Bucket
Next Topic: If not in an oracle script
Goto Forum:
  


Current Time: Mon Sep 26 23:10:37 CDT 2016

Total time taken to generate the page: 0.26299 seconds