Adding HH:MM to date compare using sysdate in WHERE 2

[ljsmith91]

I am trying to attach specific hours of the day to my WHERE clause which checks a date and time stamp of of datetime field and also uses sysdate to get the correct date. I cannot seem to get the syntax right as I am not a truw sql programmer. Can someone show me how I could code this statement up so that it would do the compare? Thanks so much.

WHERE (tdl.poll_time BETWEEN trunc(sysdate-7,'DD') 08:00 AND trunc(sysdate-6,'DD') 11:00)

 

[SkipVought]

Hi,

Something like...

WHERE (tdl.poll_time BETWEEN trunc(sysdate-7) + To_Date('08:00','hh:mm') AND trunc(sysdate-6) + To_Date('11:00','hh:mm'))

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ljsmith91]

Hi Skip,

Thanks...I gave that a try but it wouldn't allow the date fields to be added together. This is the error I get:

ORA-00975: date + date not allowed
Cause: An attempt was made to add two date fields together. Dates may be added only to numeric fields, not to other dates.
Action: Use the Oracle function TO_NUMBER to convert one of the date fields to a numeric field before adding it to the other date field.

 

[SkipVought]

I only dabble with Oracle SQL. Why not try to convert the time value to a number, then?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, that makes sense. You would NEVER add July 21, 2010 to December 25, 2010 would you? Dates/Times are 'reference points in time.'

What we can do, is add a DURATION to a point in time in order to get another point in time. The key is getting the units of duration to match the units of Date/Time.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Beilstwh]

WHERE (tdl.poll_time BETWEEN trunc(sysdate-7,'DD') + 8/24 AND trunc(sysdate-6,'DD') + 11/24)

Bill
Lead Application Developer
New York State, USA

 

[Beilstwh]

WHERE (tdl.poll_time BETWEEN trunc(sysdate-7) + 8/24 AND trunc(sysdate-6) + 11/24)

sorry, I didn't see the 'DD'

Bill
Lead Application Developer
New York State, USA

 

[ljsmith91]

Thanks so much for the responses and help. Beilstwh, that seems to have done the trick. I would have never come up with that. Big help...Thanks.