OraFAQ Forum: SQL & PL/SQL » SQL How do I return all records between two times

Home » SQL & PL/SQL » SQL & PL/SQL » SQL How do I return all records between two times

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL How do I return all records between two times [message #234395]

Tue, 01 May 2007 07:33

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.

Report message to a moderator

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

joy_division
Messages: 4963
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.

Report message to a moderator

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

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!!

Report message to a moderator

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

Bill B
Messages: 1971
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.

Report message to a moderator

Previous Topic:	Index Name in User_Indexes with No Column Name
Next Topic:	How to find this node value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 06:10:26 CST 2024