Oracle SQL Retrieve Data From End of Month - 16th And 15th - 1st

Alright so I am trying to retrieve data a field we will call DATE_OF_ENTRY and the field is like this.

Example DATE_OF_ENTRY Data

28-NOV-15

So I need to use this field in a script that will be running twice a month to pull certain records. Basically when it's the 16th day of the current month I want all the records from the 1st-15th to be pulled up. When I run this script on the 1st of the next month I want all the records from the 16th-End of last month.

What I am using now

WHERE ROUND(DATE_OF_ENTRY,'MM') = ROUND(sysdate-1,'MM') AND DATE_OF_ENTRY < trunc(sysdate)

The problem with this statement is that it works on the 1st for the 16th to End of the last month, but on the 16th it gets data from the prior month still.

Any help is appreciated!

Answers


Using TRUNC() function with MONTH parameter will get the first day of the month.

Using TRUNC() function with DATE_OF_ENTRY will remove the TIME part.

Use + operator to add days to a DATE

 SELECT TRUNC(sysdate, 'MONTH') firstDay,
        TRUNC(sysdate, 'MONTH') + 15 Day15,
        *
 FROM yourTable
 WHERE TRUNC(DATE_OF_ENTRY) >= TRUNC(sysdate, 'MONTH')
   AND TRUNC(DATE_OF_ENTRY) <= TRUNC(sysdate, 'MONTH') + 15

Need Your Help

Where is Webform Component Tab in Drupal 7 Webfrom Module?

forms module drupal-7 components drupal-webform

i want to make a custom form in drupal 7 which should contain fields like firstname,lastname, date of birth, phone, email and other fields. i download the webform module, install and enable it.

Rolling Row Subtraction

r row subtraction

I am looking to perform a row subtraction, where I have a group of individuals and I want to subtract the more recent row from the row above it (like a rolling row subtraction). Does anyone know a ...