Home » SQL & PL/SQL » SQL & PL/SQL » retrieve records for current month
retrieve records for current month [message #200548] Tue, 31 October 2006 05:49 Go to next message
rajuakula
Messages: 63
Registered: March 2005
Member
hi,

I have the following data

id code rdate
1 99 25-sep-2006
2 78 25-sep-2006
3 57 25-sep-2006
1 97 25-oct-2006
2 79 25-oct-2006
3 58 25-oct-2006
1 97 25-nov-2006
2 79 25-nov-2006
3 58 25-nov-2006

now If sysdate is 31-october-2006 month then
I need to fetch the records for october month
with rdate 25-0ct-2006 till sysdaet is 15-nov-2006.
if sysdate is 16-nov-2006 then oct records should not be pulled
In case if sysdate is 26-nov-2006 and above then
need to pull the records with rdate as 25-nov-2006 till the sysdate is 15-dec-2006.if mean If execute the query daily from
26th nov to 15th dec then I should get the rdate records

1 97 25-nov-2006
2 79 25-nov-2006
3 58 25-nov-2006

if sysdate is oct then I need if I execute the query then
from oct 26th to nov 15th then I should get

1 97 25-oct-2006
2 79 25-oct-2006
3 58 25-oct-2006

thanks
Re: retrieve records for current month [message #200557 is a reply to message #200548] Tue, 31 October 2006 06:22 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There must be something wrong with me because, reading your post, I thought you were speaking Chinese (and I don't know a single word of it).

Here's how I understood your problem; if that's not what you were looking for (and probably it is not), do say so.
SELECT * FROM raju
WHERE rdate BETWEEN TRUNC(SYSDATE, 'mm')
                AND TRUNC(ADD_MONTHS(SYSDATE, 1), 'mm') + 15;
For testing purposes (as I guess you will not wait another month to see how it works), use something like this:
SELECT * FROM raju
WHERE rdate BETWEEN TRUNC(TO_DATE('26.11.2006', 'dd.mm.yyyy'), 'mm')
                AND TRUNC(ADD_MONTHS(TO_DATE('26.11.2006', 'dd.mm.yyyy'), 1), 'mm') + 15;				
Previous Topic: How do I check for a number within a string of numbers?
Next Topic: query help
Goto Forum:
  


Current Time: Sat Dec 03 16:22:47 CST 2016

Total time taken to generate the page: 0.04765 seconds