Home » SQL & PL/SQL » SQL & PL/SQL » where clause for previous month date (oracle 9i)
where clause for previous month date [message #410919] Wed, 01 July 2009 01:11 Go to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
hi all,
1 date of every month i run one job,
this job populates data in text file
this job populates the data of employee which is enroll between 22 to 30 or 31 of previous month
so how sholud i write 'where' clause to populate enroll employee of previous month 22 to 31 0r 30,
this job runs on 1 st of every month
please guide
Re: where clause for previous month date [message #410923 is a reply to message #410919] Wed, 01 July 2009 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ADD_MONTHS

Will you post one question for each date function?
Please read them all then you can do it yourself.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i88891

And please when you get an answer, give a feedback.

Regards
Michel
Re: where clause for previous month date [message #410926 is a reply to message #410923] Wed, 01 July 2009 01:22 Go to previous messageGo to next message
vivek_rol
Messages: 65
Registered: February 2009
Member
michel
its not possbile with ADD_MONTHS
Re: where clause for previous month date [message #410934 is a reply to message #410919] Wed, 01 July 2009 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vivek_rol
populate enroll employee of previous month 22 to 31 0r 30
What do you do in February?

vivek_rol
its not possbile with ADD_MONTHS
Why not? "Previous month" = ADD_MONTHS(this month, -1).
Re: where clause for previous month date [message #411113 is a reply to message #410919] Thu, 02 July 2009 00:44 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I think, it should be LAST DAY of month rather then 30 or 31.

regards,
Delna
Re: where clause for previous month date [message #411115 is a reply to message #411113] Thu, 02 July 2009 01:06 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
Hi,
Try with below code.

select * from emp
where
hiredate between add_months(to_date(sysdate),-1)+21 and last_day(add_months(TO_DATE(sysdate),-1))
Re: where clause for previous month date [message #411120 is a reply to message #411115] Thu, 02 July 2009 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We all know how do it, we gave all the information to do it.

Please read OraFAQ Forum Guide and follow it.

Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


"How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: where clause for previous month date [message #411126 is a reply to message #411115] Thu, 02 July 2009 01:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ds285269 wrote on Thu, 02 July 2009 08:06
Hi,
Try with below code.

select * from emp
where
hiredate between add_months(to_date(sysdate),-1)+21 and last_day(add_months(TO_DATE(sysdate),-1))


Why would you convert sysdate to a date? It requires Oracle to first do an implicit to_char.
Re: where clause for previous month date [message #411156 is a reply to message #410919] Thu, 02 July 2009 03:23 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SELECT 1
  FROM dual
 WHERE TRIM (sysdate) BETWEEN TO_DATE ('22/07/2009', 'dd/mm/yyyy')
                        AND TO_DATE ('01/08/2009', 'dd/mm/yyyy') - 1
Re: where clause for previous month date [message #411161 is a reply to message #411156] Thu, 02 July 2009 03:35 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
TRUNC instead of TRIM
Previous Topic: Deletion from tables as new tables are added to schema
Next Topic: Oracle Analytic Function
Goto Forum:
  


Current Time: Sun Dec 04 18:44:34 CST 2016

Total time taken to generate the page: 0.07194 seconds