| Set time parameter in date/time field [message #435667] |
Thu, 17 December 2009 05:16  |
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 #435672 is a reply to message #435667] |
Thu, 17 December 2009 05:33  |
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 #435681 is a reply to message #435668] |
Thu, 17 December 2009 06:13 |
davea300
Messages: 2
Registered: December 2009
|
Junior Member |
|
|
Michel Cadot wrote on Thu, 17 December 2009 05:20Do 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 |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
