-2

I'm trying to find out how to download data between two dates. For example, I want the code to get the data between today and 7 days ago. Right now I can only get it to get data from one day.

SELECT "SITE_CODE", "LOG_ID", "SHIFT_ID", "SHIFT_DATE", "ENTRY_TIMESTAMP", "ANNOTATION_NUMBER", "DOCUMENT_NUMBER", "DOCUMENT_ORDER", "DOCUMENT_TYPE", "DOCUMENT_DESCRIPTION", "DOCUMENT_FILENAME", "DOCUMENT_BLOB" FROM(
/* + NO_PARALLEL */select * from (
select  * from (
SELECT ROWID "ROWID", ORA_ROWSCN "ORA_ROWSCN", SITE_CODE SITE_CODE, LOG_ID LOG_ID, SHIFT_ID SHIFT_ID, SHIFT_DATE SHIFT_DATE, ENTRY_TIMESTAMP ENTRY_TIMESTAMP, ANNOTATION_NUMBER ANNOTATION_NUMBER, DOCUMENT_NUMBER DOCUMENT_NUMBER, DOCUMENT_ORDER DOCUMENT_ORDER, DOCUMENT_TYPE DOCUMENT_TYPE, DOCUMENT_DESCRIPTION DOCUMENT_DESCRIPTION, DOCUMENT_FILENAME DOCUMENT_FILENAME, DOCUMENT_BLOB DOCUMENT_BLOB FROM "ESOMSP"."LOG_ENTRY_DOCUMENTS"
 )  WHERE ENTRY_TIMESTAMP  = TO_DATE('2009-02-18 10:44:10', 'YYYY-MM-DD HH24:MI:SS')
 ) sub1 order by 7 asc
)
sticky bit
  • 36,626
  • 12
  • 31
  • 42
Matt Friend
  • 35
  • 5

1 Answers1

0

Subtracting an intger n from a date will subtract n days from it. Use trunc() to set the hour, minute, second ... portion of a date to all zero.

So to filter for every entry_timestamp from the last seven days you could write:

...
WHERE entry_timestamp >= trunc(sysdate - 7)
      AND entry_timestamp < trunc(sysdate)
...
sticky bit
  • 36,626
  • 12
  • 31
  • 42