Toad World® Forums

Between statement...help please


#1

I am new to toad and haven’t mastered this yet

I am trying to query a date & time field to extract all records that fall between a specific time within a range of dates.

for example i would like to see all records created between 17:00 and 07:00 in the date range 01/01/2008 to 31/12/2008.

please help…


#2

What type of database are you connected to? Oracle, SQL Server, DB2, MySQL?


#3

It’s an ORACLE database


#4

The Query Builder has a date range picker that generates the between statement for you for the most common ranges.
Debbie
QBDateRange.bmp


#5

Thanks Debbie, but I was looking to call a range of times within a certain range of dates.

i.e. only the records that were created between 17:00 and 06:00 within the date range last full year. The date & time field are the same and not seperate.


#6

That you will need to do through something like below.

Date in 2008

time after 5:00 PM or before 7:00 AM

select * from mytable

where (trunc (mydate) between DATE ‘2008-01-01’ and DATE ‘2008-12-31’)

and (to_number (to_char (my_date, ‘HH24MI’)) >= 1700

 or to_number (to_char (my_date, 'HH24MI'))