CODEWITHSUNDEEP
oracle-database
Muhammad Tariq Akram
Muhammad Tariq Akram

Reputation: 1

how do I get the Specific date of the current month in Oracle?

I need to run a batch job, which will process all the record in the database table which has been created before the 14th of current month. In java i know, but i am hitting Database server directly via Oracle query, so i need to match the record created date with the current month's date of 14th.

Batch job can be run on any date like 17, 18 or 29 etc but record should be return only which created before 14th of current month, not able to find any help for this.

like in java

Calendar date = Calendar.getInstance();
date.set(Calendar.DAY_OF_MONTH, 14);

Upvotes: 0

Views: 1999

Answers (2)

Boneist
Boneist

Reputation: 23588

You can easily find the 14th of the current month by using TRUNC(). Here is an example:

select sysdate,
       trunc(sysdate, 'mm') + 13 date_to_query_before
from dual;

SYSDATE               DATE_TO_QUERY_BEFORE 
--------------------- ---------------------
25/06/2015 15:23:55   14/06/2015 00:00:00

Upvotes: 2

Michael Broughton
Michael Broughton

Reputation: 4055

You can truncate a date to the month using the correct format string in the TRUNC, then add 14.

So:

SELECT TRUNC(SYSDATE,'mm')+13 FROM DUAL;

Gives the 14th of the current month. So you could select for data between the 1st and 14th (you said BEFORE the 14th) with

SELECT * 
  FROM my_table 
 WHERE date_created >= TRUNC(SYSDATE,'mm') 
   AND date_Created < TRUNC(SYSDATE,'mm')+13;

EDITED to fix stupid math error....

Upvotes: 1

Related Questions