1

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!

smurfguy
  • 45
  • 1
  • 6

1 Answers1

0

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
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • While this code may answer the question, it would be better to explain how it solves the problem without introducing others and why to use it. Code-only answers are not useful in the long run. – JNYRanger Dec 01 '15 at 19:50
  • Alright I will go ahead and see if this solves the issue. – smurfguy Dec 01 '15 at 20:29
  • Ok. I kinda see where this was going. I was able to manipulate this a little bit if I used - 1 on the sysdate. Maybe if you explained more I could see how to modify this. Anyhow just to remind you guys. Since today is the 1st of December, I run the sql, I would expect to see data from November 16th to the 30. When I run this script later in the month on December 16th I want data from Dec 1st to Dec 15th. – smurfguy Dec 01 '15 at 20:53
  • Sorry this is only for the 1st to 15th, I tought you already solve 16 to 30 ? – Juan Carlos Oropeza Dec 01 '15 at 20:55
  • Well yes that where clause I used earlier was suppose to work for the entire 2 instance, but anyhow I suppose I can just have 2 separate scripts. I really wanted to be able to do this in 1 script though. – smurfguy Dec 02 '15 at 16:27