Why not to use the Months_Between function:
Firstly you should visit <a href=”http://www.sql-performance-tuning.com/oracle-sql-functions/”>this</a> website, where you will find complete list of Oracle sql functions and <a href=”http://www.sql-performance-tuning.com/oracle-date-format/”>this</a> website where you will find complete list of Oracle date format elements. This should also help you for your future work.
<b>SQL function MONTHS_BETWEEN</b>
The MONTHS_BETWEEN function calculates the number of months between two dates. When the two dates have the same day component or are both the last day of the month, then the return value is a whole number. Otherwise, the return value includes a fraction that considers the difference in the days based on a 31-day month. The return value is positive when the first date is later than the second date, and negative when the first date is earlier than the second date.
You can take the “from” date and either make it the 1st of that month, or make it the same day as the “to” date, depending on the desired result. If you are setting the “from” day to be the same as the “to” day, you need to handle the special case of when the “to” date is in the range 29-31, since the “from” month may not contain that day.
If the day is the same in both the “from” and “to” dates (or if both are the last day of the respective month), the result will be an integer number of months. If the “from” day is not the same as the “to” day, the result will be a number like “xx.yy”, where “xx” will be the number of full months, and “yy” will be the fractional number of days (assuming a 31-day month = 1.0).
Since Months_Between accepts both datetime objects and text strings that are interpretable as dates, you can use your input formats with some simple string manipulation, or you can convert them to dates, e.g.
<pre>nBetween = Months_Between(todate, ’01-’ || fromdate);</pre>
Note that Months_Between returns a positive result when the 1st argument is a “later” date than the 2nd argument.
KCCrosser – Sorry for the above. That is what happens when you skim the question and read some answers without double-checking the real question…
Assumptions: You have a “FROM_DATE” value in the form “mmm-yyyy” and a “TO_DATE” value in the form “dd-mmm-yyyy”. You want to retrieve all records from a table that includes a date/time field (call it “RECORD_DATE”) where that date/time field falls between the 1st day of the FROM_DATE month and the TO_DATE.
Note – possible options that you need to consider: Do you want to include only months prior to the 1st day of the TO_DATE month? Do you want to include records for a partial month up to just before the actual date of TO_DATE? Do you want to include records that have a date/time falling anywhere on the actual day of TO_DATE? Do you want to include records through the end of the month in which TO_DATE falls?
Assuming that you want records up to just before TO_DATE (i.e., records up until 23:59:59.99999… on the day before TO_DATE), then the simple solution is to first convert the FROM_DATE and TO_DATE values into valid date/times and then use those in the query.
Also – minor date/time trick – it is usually best to formulate date/time queries using a where clause of the form:
where RECORD_DATE >= FROM_DATE and RECORD_DATE < TO_DATE
Why? Because there is no such thing as the “last moment in time on day xxx”, but there is a value 00:00:00.0 that is the absolute start of a new date.
sFROM_DATE varchar(255) := ‘Jan-2010′; — contains ‘mmm-yyyy’
sTO_DATE varchar(255) := ’17-Aug-2010′; — contains ‘dd-mmm-yyyy’
– force 1st day of month and convert to datetime value
dtFROM_DATETIME := to_date(’01-’ || sFROM_DATE, ‘dd-mon-yyyy’);
– convert to date/time, then force to 00:00:00 of “next” day
– Oracle represents date/time values where the integer part is a day, and the fractional
– part is the hours/minutes/seconds – adding 1 gets us into the next day, and truncating
– yields the 00:00:00 point of that day
dtTO_DATETIME := trunc(to_date(sTO_DATE, ‘dd-mon-yyyy’) + 1);
– now you can get the records between the dates (inclusive) with:
and RECORD_DATE >= dtFROM_DATE
and RECORD_DATE < dtTO_DATE;
If you wanted only records before the TO_DATE, then the “+1″ and “trunc” parts aren’t needed.
If you wanted records only to the end of the prior month, a simple technique would be to replace the day part of the TO_DATE with “01″:
dtTO_DATETIME := to_date(’01′ || substr(sTO_DATE,3), ‘dd-mon-yyyy’);
If you want to include all the month in which TO_DATE falls, I would use the previous statement to get the 1st day of the month in which it falls, then use ADD_MONTHS to get the next month:
dtTO_DATETIME := ADD_MONTHS(dtTO_DATETIME, 1);