Home » SQL & PL/SQL » SQL & PL/SQL » SQL How do I return all records between two times
SQL How do I return all records between two times [message #234395] Tue, 01 May 2007 07:33 Go to next message
sync_or_swim
Messages: 2
Registered: May 2007
Junior Member
Hi,

Apologies if this is the wrong forum for such a basic question but I have Googled this and searched the FAQ on this site but I cant seem to find a straight answer.

All I want to do is to retrieve all records from a table using times as the criteria. When a record is added/updated or deleted, a trigger inserts an entry into a log table:

TBL_LOG
LOGDATE DATE
MODI_TYPE CHAR(1)
RECORDNUMBER VARCHAR2(18)

I have written a simple VB6 program which extracts all records from my database and converts them to XML format. Once this bulk upload has been done I just want to check the log table to see which records have changed since the program was last run. If the program is set to run as a scheduled task, say every 30 minutes, how would I construct the SQL statement to return all records with a logdate between now and 29 minutes ago?

I have used trunc in the past:
where TRUNC(logdate) between date1 and date2

But how do I include the time in this statement, if I remove the trunc command add the time to the end of the date I get the error:
Date format picture ends before converting entire input string.

Thanks in advance.
Re: SQL How do I return all records between two times [message #234399 is a reply to message #234395] Tue, 01 May 2007 07:58 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
sync_or_swim wrote on Tue, 01 May 2007 08:33

But how do I include the time in this statement, if I remove the trunc command add the time to the end of the date I get the error:
Date format picture ends before converting entire input string.

Thanks in advance.


Of course you do, because you are comparing DATEs to character strings.

Use

xdate between to_date('01-dec-2006 13:00:00','DD-MON-YYYY hh24:mi:ss')
and to_date('10-dec-2006 21:00:00','DD-MON-YYYY hh24:mi:ss')


for dates between 1pm on Dec 1 2006 and 9pm on Dec 10 2006.
Re: SQL How do I return all records between two times [message #234402 is a reply to message #234399] Tue, 01 May 2007 08:13 Go to previous messageGo to next message
sync_or_swim
Messages: 2
Registered: May 2007
Junior Member
As easy as that!!!

Thank you very much indeed, exactly what I wanted, a plain English answer!!
Re: SQL How do I return all records between two times [message #234437 is a reply to message #234395] Tue, 01 May 2007 10:27 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
or to make it dynamic,

select *
from my_table
where xdate between sysdate - (1799/86400) and sysdate;



1799 is the number of seconds in 29 minutes and 59 seconds and 86400 is the number of seconds in a day.
Previous Topic: Index Name in User_Indexes with No Column Name
Next Topic: How to find this node value
Goto Forum:
  


Current Time: Sat Dec 10 01:05:30 CST 2016

Total time taken to generate the page: 0.09689 seconds