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