Oracle also has the TO_CHAR function, so it allows TO_CHAR(whn, 'yyyymm'), for example. 0 is for 1/1/1900, and getdate is the current date --(i used a set date bc dates will change as this post gets older). Note that it is subtracting the second from the first, so you must have them in this order. 0- 64bit Production With the Partitioning and Data Mining options'. SELECT DATEADD (month, 1, '20220730'); The below DATEADD will add 1 year to the provided date value, the year changed from 2022 to 2023. The Question asked for "6 months from the system date". In a table called ‘Employees’ with columns ‘EmployeeID’, ‘Name’, and ‘HireDate’, we can calculate the number of months since each. Admission_Date) > 5. Can be one of the following values:1 Answer. g. Share. I have to get the date from the system and calculate the date difference in days. 10 months ago by below86. Compare dates problems. I have two Oracle columns (in a VIEW, not a table) - DateOfBirth and MembershipDate. Delaying Execution. SELECT FIRST_NAME, HIRE_DATE, SYSDATE, SYSDATE - HIRE_DATE. Javascript. And this may raise an issue for leapers (someone born on the 29th of Feb), as taken from Wikipedia: The effective legal date of a leapling's birthday in non-leap years varies between jurisdictions. For example when I place 01/01/04 as the. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the. Also, to get the end date for the quarter, just use: select dateadd (quarter, datediff (quarter, 0, SomeDate)+1,0) -1. 3. This is from my own library, will return the difference of months between two dates. ie if the month when the query is run is march 2013. Sorted by: 10. . So to calculate the number of day from a date to the end of the month you could write. Current Date/Time. See the valid date parts in. Date Interval Calculation: The number of specified date parts between two provided dates is found using the DATEDIFF () method. year, month, day), it takes into account only a certain element and higher elements in the time hierarchy – but not lower elements. 0208 days. Apr 2, 2013 at 6:13. So I'm guessing it is an available function for mysql, but not for oracle databases. For example, with some sample dates: select months_between (date '2015-03-19', date '2012-01-06') as month_diff from dual; MONTH_DIFF ---------- 38. The default is date format 1. I have a table where PurchasedDate is maintained. 18. (The valid formats 1,7,10,11 will be described later. 0. Currently I am only returning 1. date1. And what date you want to get for example difference between dates 456 days it's mean: 1 year 3 month and several days. How can we. (CASE WHEN Trips. If you omit fmt, then date is rounded to the. Asked 2 years, 9 months ago. DATEDIFF accepts either. date_to, DATEDIFF(DD, evnt. 0 days. , YEAR, MONTH, DAY,. The system always returns a positive number regardless of which date. DATEDIFF is a common function in the SQL Server to find the number of days between two dates. To calculate the number of days between date1 and date2, you can use either Day of. Answer: Oracle supports date arithmetic and you can make expressions like "date1 - date2" using date subtraction to get the difference between the two dates. i need to find the difference of two dates as the number of months: find the last day of the month of both the dates. EXTRACT (DAY from (first_date - second_date)) If for some reason, you do want to do that, then I don't think there is a datatype for the "datefield" keyword (YEAR, MONTH, DAY, etc). . This will return an interval that may look something like “1 year 2 months 3 days”. Table 9. Select DATEDIFF (MM,0,GETDATE ()) Output - 1374, If getdate () output is "2014-07-23 19:33:46. The table contains the data about each staff member’s. Let’s see a few examples of SQL subtract date from the DATEADD function: In the below example, we are adding one month to the existing date ‘20220730’. 0 months apart:Asked 3 years, 4 months ago. --Changing current database to the Master database allows function to be shared by everyone. The add_days variable can be negative. We would like to show you a description here but the site won’t allow us. SELECT AVG (CAST (DATEDIFF (d, DateUsed, DateExpires) AS FLOAT)) FORM tbl. *Or even the CTIME. #1633022. Asked 5 years, 9 months ago. UPDATE Yes, he will get a 1 month difference even if it is only 1 day difference, but OP. This is going to be short:. DateDiff<firstWeekDay>(Date1, Date2, Unit). DATEDIFF(Day, MIN(joindate), MAX(joindate)) AS DateDifferen. lastModified and w. The first week of the year is 1. select *, cast ( (cast (begin_date as date) - cast (end_date as date) YEAR) as decimal (3,2)) AS year_diff from x. DATEDIFF with examples DATEDIFF function accepts 3 parameters, first is datepart (can be an year, quarter, month, day, hour etc. 3 Answers. oracle difference between two dates in years Code Example. If this format has been used to. I am trying to come up with a way in Oracle to calculate the difference between dates in days. If you want to format it as you wanted (note that mm format mask is for months; mi is for minutes), then you could do some extracting - again from timestamp (won't work for date):. NEW_TIME (date, timezone1, timezone2) Timestamp. What's the function in Oracle that would be the equivalent to DATEDIFF in the SQL Server? On Oracle, it is an arithmetic issue: Select DATE1. Example 3: Comparing dates for elapsed time (in months) since a given date: Using the DATEDIFF function, we can calculate the elapsed time in months from a specific date until the current date. The function will always return the difference between two dates in days. So, it starts with RIGHT(“00”. SQL was invented in the 1970s by IBM Computer Scientists. InDays)) % (Pattern. 7. Try SELECT SYSTIMESTAMP + INTERVAL '5' MINUTE, SYSTIMESTAMP + 5 / 24 / 60 to convince yourself. Functions, you can call DateDiffDay: var ans = from t in Table1 group t by 1 into tg select tg. How to generate days, weeks, or months between two dates in Oracle Database June 1, 2021 | 25 minute read Chris Saxon Developer Advocate There are. DateDiff. sql. InDays) - (Pattern. 1. The syntax for using the DATEDIFF function in Google BigQuery looks like the following:. g. 1. However, since you have known start- and end-dates then you can find the number of full years between the two bounds and then use an INTERVAL DAY TO. Asked 11 years, 5 months ago. 1. Datediff (cast ('2020-08-01' as Date),Cast ('2020-07-31' as Date)) => 1. 1. PROJECTID, PROJ_TASK. date]-90) - my comparison subtracts 90 days from the max date Yes, I know,. In this article, we take an existing date and the current date and using the "GetDate" function to find the years, months and days. DATEDIFF with examples DATEDIFF function accepts 3 parameters, first is datepart (can be an year, quarter, month, day, hour etc. I tried this in a query. SYSTIMESTAMP). Refer to the tables below to determine the formats you can enter in the box. The number is the number of seconds elapsed since midnight, January 1, 1970. then just use it in the script eg. sql. Hi, Everyone, I am trying to find out the difference in days between two dates and the query i am passing is SELECT TO_char(sysdate, 'dd/mm/yyyy')-TO_char('15/11/2011', 'dd/mm/yyyy') DAYS FROM DUAL. I want something like this. I can get these two dates from the database in 07-JUN-12 04. , year, quarter, month, day, hour, minute, second), startdate is the starting date or time, and enddate is the ending date or time. montant_annuel = NEW. Outputs the fraction of days between two dates: DIFFERENCE ---------- 1. Syntax. Subtracting two dates will return if and only if they are the same year, the same month, the same day, the same hour, the same minute, and the same second. CASE WHEN a. Dates are always a joy to work with in any programming language, SQL not excluded. sample: SELECT months_between(column1,column2) FROM Table Share. In Oracle, MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number. SELECT DATEADD ( quarter, DATEDIFF. In this article, I would like to show the difference between two dates in years, months and days in SQL Server. Oracle offers its own solution, although does not have the DATEDIFF function. If they are identical down to the second then, presumably, returning 0 is the right answer. MonthDiff = DateDiff ("m", startdate, enddate) End If. Oracle's MONTHS_BETWEEN on the other hand tries to calculate an exact difference in months' fractions: select months_between(date '2021-02-01', date '2021-01-31') from dual; => 0. A custom option dialog displays with an empty box. Anybody help what is the equivalent of Dateadd and Datediff in DBeaver or anybody can convert this code in DBeaver. for oracle: months_between. public static int MonthDiff (DateTime d1, DateTime d2) { int retVal = 0; // Calculate the number of years represented and multiply by 12 // Substract the month number from the total // Substract the difference of the second month and 12 from the total retVal. DP_MONTHは、入力日付をキャプチャする月の間隔を返します。(2006年10月- 2005年6月= 16)CONCAT((DATEDIFF(Minute,StartDate,EndDate)/60),':', (DATEDIFF(Minute,StartDate,EndDate)%60)) TimeTaken , and instead of calculating time difference once again using datediff if I can use the time taken value column which is already calculated the query execution will be faster. I want to calculate the difference between two dates in OBIEE10g in Year/Months/Days format similar to the output of below SQL. DP_MONTH returns the distance between the months capturing the input dates. FLOOR (DATEDIFF (DAY, date_of_birth, reference_date) / 365. Improve this answer. If you want to confirm, simply run this. For example, from 2/10 to 3/10 is considered one month, and from 2/10 to 3/15 is also considered one month. SELECT DATEDIFF (second, '2019-12-31 23:59:59', '2020-01-01 00:00:00'); A value of 1 is returned because the boundary of seconds is. I recommend you have a stab yourself. You also wouldn't prefix a function name with the @ sign. Then, we need a 2-digit month string. answered May 15, 2013 at 7:21. 4 Answers. The first day of the year is 1. CREATE OR REPLACE FUNCTION dateDiff ( p_dt1 IN DATE, p_dt2 IN DATE ) RETURN NUMBER IS BEGIN RETURN p_dt1 - p_dt2; END; datediff('MONTH', pr. Push out all due dates by one week. . Modified 5 years, 9 months ago. I tried this: TO_DATE (sysdate) - TO_DATE (thedate, 'YYYY-MM-DD') < 5. Join our newsletter and get access to exclusive content every month. We need to pad a zero for single-digit months. Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time. The idea is to find how many years, months, and days they have served there. So it's not an accumulative difference, it's only when a date is within a smaller month. for the month differnce the standard sql is DATEDIFF, in this function you must pass 3 params, if you must calculate the difference from 2 columns, c1 and c2, you must do this query SELECT DATEDIFF(month,c1 , c2) FROM T WHERE. You can simply subtract two dates. Use DATEADD (): where HireDate < dateadd (year, -3, GETDATE ()) DATEDIFF () does not do what you think it does. 1. SS, which computes the difference in seconds. For instance, select datediff (day,'1 Jan 2000' ,'18 April 2014') / 365. getYear () * 12 + date1. But when I searched for it, it wasn't available. If date1 is earlier than date2, then the result is negative. SELECT EOMONTH ('02/04/2016') Returns 02/29/2016. 1. SQR for PeopleSoft provides an extensive set of date edit masks. SELECT floor ( months_between ( date_1, date_2 )/12) floor_years_between FROM employee. 365 days elapse between the two dates. ) The dates and their formats are separated by a comma (,) and are defined in the Data field of the. Date parts examples are day, week,. from a date time value. Since the value does not equal an entire month the result is 0. Instead, use the dateAdd function on todays date, and compare the database table column to the result of that single calculation. Few examples of DATEDIFF: DATEDIFF - Example 1 Here, in this example, datepart is "day": SELECT DATEDIFF(day,'2016-06-05','2016-08-05') AS DiffDate Result: DiffDate 61 If the endDate has a day part less than startDate, it will get pushed to the previous month, thus datediff will give the correct number of months. 000 READDATE < DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 10, 0). TIMESTAMPADD () manipulates data of the data types DATE and DATETIME based on a calendar year. 25 = 0. To break the diff between 2 dates into days, hours, minutes, sec -- you can use the following: Dy gives you number of days between 2 dates (partial days discarded). DATE is the main – or rather, original – datatype used in Oracle for holding dates. Modified 1 year, 3 months ago. 0. SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate from dual is not working in SQL ERROR : ORA-00904: "DATEDIFF": invalid identifier 00904. Goal. The math is 100% accurate for dates within a couple of hundred years or so. Teams. Sorted by: 0. AT TIME ZONE. Note that it is subtracting the second from the first, so you must have them in this order. If day number from start date is higher than day number from current date: days are negative. ) and rest are two dates which you want to compare. All the same concepts and ideas and things to watch out for apply as grouping by month, only instead of using month we use quarter. . However, The. buf 1 declare 2 total_days integer; 3 total_weeks integer; 4 remaining_days integer; 5 begin 6 total_days := date '2014-12-16' - date '2014-12-01'; 7. I don't think that you need to write your own timestampdiff function since oracle already has one: EXTRACT. If the endDate has a day part less than startDate, it will get pushed to the previous month, thus datediff will give the correct number of months. Enter a date format string that describes the Date2 parameter. id INNER JOIN al ON ac. lastModified and w. The number you’re left with is the number of days between the two days. But since it's a finite set, you can just get. Oracle SQL - CASE in a WHERE clause. 32. However, The problem is different number of records because DATEDIFF(month,a,b)<=4 in SQL Server returns only the month difference whereas months_between(a,b)<=4 in oracle returns the. subtract and give the difference in number of months. Ecco una semplice funzione per calcolare la differenza tra due date. Get the number of months between two dates. Date Part Flexibility: Years, months, days, hours, minutes, and seconds are among the date parts that are supported. getMonth (); int m2 = date2. e. Tarek Fathy Nov 18 2010 — edited Nov 18 2010. Asked 8 years, 2 months ago. , YEAR, MONTH, DAY,. Right-click and select Convert to Date. Also, according to Oracle's documentation LAST_DAY returns a DATE. The SQL DATEDIFF() function is an in-built function in SQL that is used to return the difference (as a signed integer value) between two dates or times. As another option you could use the months_between () function, and manipulate the value that gives you into the three. 14977 Days 7 Hours 36 Minutes 56 Seconds. Just divide the total by 7 to get the weeks and then calculate the remaining days. I hope you will like the SQL Date Functions Examples article. Month-difference between any given two dates: I'm surprised this hasn't been mentioned yet: Have a look at the TIMESTAMPDIFF() function in MySQL. Modified 6 years, 5 months ago. January 30, 2004 - 7:26 pm UTC. As you can see it means that year should be -1, and month 0. The following table lists all the valid datepart values. MONTHS_BETWEEN = 1. Just out of curiosity, have you paid the $95 for a manual yet? ;-) If not, get the thicker one when you do unless you are pretty familiar with the commands and just need a reference. 1. select trunc ( (sysdate - to_date ('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) / 24) from dual; You should have multiplied by. SELECT DATEDIFF (month,'2011-03-07' , '2021-06-24'); In this above example, you can find the number of months between the date of starting and ending. See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact. FLOOR (DATEDIFF (DAY, date_of_birth, reference_date) / 365. For both DATEDIFF and minus sign: Output values can be negative, for example, -12 days. SS: 差異を秒数で計算します。. SQL DATEPART. 0. 4193548. The following example returns 2 for the months between the two dates:. Sep 16, 2015 at 5:55. current_time() returns the date and time on the database client side. SYSDATE) and subtracting a date from a timestamp (e. g. 1. Improve this answer. I have to change it to '18-JAN-02' to get those. Modified 2 years, 6 months ago. SELECT DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()),-3) Executes. You don't need to generate all the days as it'll be inefficient; just use a recursive sub-query factoring clause to iterate over each month: WITH months (. e. January 1 of any year defines the starting number for the week datepart, for example: DATEPART (wk, 'Jan 1, xxxx') = 1, where xxxx is any year. Warning: If the Start_date is greater than the End_date, the result will be #NUM!. This field level rule lets you display the difference between two dates. In this, we will add the number of years that we expect this person to turn in a given year, based on the DATEDIFF( ) results. In Oracle, MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number. 9. by converting the DATEDIFF() first and then. DateDiff. From the Oracle documentation: MONTHS_BETWEEN returns number of months between dates date1 and date2. Multiply by 24 -- hours, multiply by 60 minutes, multiply by 60 -- seconds. SQL Server ignores that this is just one day. Also, you can check this for minutes : Oracle : how to subtract two dates and get minutes of the result. Share. Here's an example. You are dividing datediff / 30 which is integer division, the result will be an int. SELECT --Start with total number of days including weekends (DATEDIFF (dd,@StartDate,@EndDate)+1) --Subtact 2 days for each full weekend (DATEDIFF (wk,@StartDate,@EndDate)*2) --If StartDate is a Sunday, Subtract 1 ELSE 0 END) --If EndDate is a Saturday, Subtract 1 FROM dual. One practical example of using the DATEDIFF function in SQL Server is in a WHERE clause by selecting all employees in the AdventureWorks2008R2 database whose date of hire was in March 2003. Oracle datediff function. Access Function: DateDiff ("m",PAID_DATE,STATIC_DATE) AS Months_Between. That said, you can use the following to determine if the date falls on a weekend: SELECT DATENAME (dw,GETDATE ()) -- Friday SELECT DATEPART (dw,GETDATE ()) -- 6. If date1 and date2 are either the same days of the month or both last days of months, then the. 0. Is there another alternative for sql oracle? Is it in this case TRUNC and if so how? SELECT DISTINCT al. 9. startdate, SYSDATE) Keep in mind that MONTHS_BETWEEN. Factor by 24 to get hours, 24*60 to get minutes, 24*60*60 to get seconds (that's as small as dates go). And you can use intervals, but using month intervals for. January 30, 2004 - 7:26 pm UTC. How can I find the difference between two date columns in Oracle? Answer: Oracle supports date arithmetic and you can make expressions like "date1 – date2" using date subtraction to get the difference between the two dates. ToDate)); My SQL to LINQ Recipe might help you with some translation issues in the future. 2258064516. 0. 1. This field level rule lets you display the difference between two dates. The weekday (dw) datepart returns a number that. The next example will show the differences between two dates for each specific datapart and abbreviation. TIMESTAMP Functions. A) Get the difference in months of dates on the same day The following example returns a difference in months between two dates: July 01 2017 and January 01 2017: SELECT MONTHS_BETWEEN ( DATE '2017-07-01', DATE '2017-01-01' ) MONTH_DIFF FROM. In Oracle SQL, I want to display the difference between two dates in the format 'x years y months z days'. You can even find the number of hours, minutes, seconds, and so on in terms of details in between the two. id = al. 1. Modified 8 years ago. Returns NULL if either of <date_1> or <date_2> is NULL. Examples of Results in Months. I m used to sql server. 365 / 365. @Mohit224 - If you are saying that you want to ignore any fractional part of a year, then you. trunc ( months_between ( end_date, start_date ) /12 ) Years, mod ( trunc ( months_between ( end_date, start_date ) ), 12 ) months, end_date - add_months (start_date,trunc ( months_between (end_date,start_date) )) days. SELECT DISTINCT Test, FIRST_VALUE (CONVERT(DATE, datetime)) OVER (ORDER BY datetime DESC) StartDate,. 4 Answers. from a date time value. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Modified 11 years, 3 months ago. You can use this methodology to determine the first day of 3 months ago, and the last day of the previous month: select DATEADD (MONTH, DATEDIFF (MONTH, 0, GETDATE ())-3, 0) --First day of 3 months ago select DATEADD (MONTH, DATEDIFF (MONTH, -1, GETDATE ())-1, -1) --Last Day of previous month. In SQL Server 2012 you can use EOMONTH (Transact-SQL) to get the last day of the month and then you can use DAY (Transact-SQL) to get the number of days in the month. DiffMonths ("7/15/95") 0. If this query is executed between December 26 and December 31, you'll still. How to calculate years of experience from date_of_hiring to till date in months of an employee using sql query. user637544 Jun 1 2009 — edited Jun 1 2009. 9. This function is used to find the difference between two specified values of date. g. 000000. +1 This will work for every year difference. It can be used to do date math as well. Assuming that a. e it takes the quarter in which the start date exists and subtracts it from the quarter in which the end date exists. 2 Answers. 2323 days) multiply by 24 = hours, another 60 = minutes, another 60 = seconds. The following example illustrates how to use the. Computes the number of months between <date_1> and <date_2>. Delaying Execution. The part to return. 0 for the month differnce the standard sql is DATEDIFF, in this function you must pass 3 params, if you must calculate the difference from 2 columns, c1 and c2, you must do this query . The system calculates the number of complete months between given dates. · User1416329745 posted ANSI SQL 1992. The name of the order date month is printed, followed by the day of the month, a comma, and four-digit year. Thanks in advance for any help; this has been driving us crazy for a few months now. Learn how to use The SQL Server DATEDIFF() Function to return the difference between two dates. Then use these rules to set the column values: Start date: for the first row, return the input date; otherwise return the first of the month; End date: get the month start for the next row and subtract one day from it. MANAGER_ID, PROJECTED_DATE. . Enter a date format string that describes the Date2 parameter. CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips. CREATE OR REPLACE FUNCTION datediff (p_what IN VARCHAR2, p_d1 IN DATE, p_d2 IN DATE) RETURN NUMBER /* Updated to reflect current database and PL/SQL functionality */ AS BEGIN RETURN (p_d2 - p_d1) * CASE UPPER (p_what) WHEN 'SS' THEN 24 * 60 * 60 WHEN 'MI' THEN 24 * 60 WHEN 'HH' THEN 24 ELSE NULL END; END;SELECT months_between ( date_1, date_2 )/12 years_between FROM employee. 0. In certain cases, such as string-based comparisons or when a result depends on a different timestamp format than is set in the session parameters, we recommend explicitly converting. As you can see it means that month. 取得一季中的第一天：. Remarks. Date. If the days of the month are both the last day of the month (e. Viewed 4k times 1 I have a table with below structure. This page provides you with the most commonly used Oracle date functions that help you handle date and time data easily and more effectively. This field level rule lets you display the difference between two dates. Oracle Help Center - MONTHS_BETWEENyou ca also use LAG analytical function to get the desired results as : Suppose below is your input table: id account_number account_date 1 1001 9/10/2011 2 2001 9/1/2011 3 2001 9/3/2011 4 1001 9/12/2011 5 3001 9/18/2011 6 1001 9/20/2011 select id,account_number,account_date, datediff(day,lag(account_date,1) over (partition by. As you can see the EOMONTH function takes into account leap year. The Days, Months, and Years parameters can be negative or positive. If this solve your problem, here's the sql server syntax, just replace the variable @yourDate with your column name. It supports a large number of languages and application development frameworks. Comparison with Excel DATEDIFF function. SQL Server DATEDIFF ( DATEPART , STARTDATE , ENDDATE ) Example: Select Datediff(mm,column1,column2) Above script will output difference in months, so based. 8. SELECT --Start with total number of days including weekends (DATEDIFF (dd,@StartDate,@EndDate)+1) --Subtact 2 days for each full weekend (DATEDIFF (wk,@StartDate,@EndDate)*2) --If StartDate is a Sunday, Subtract 1 ELSE 0 END) --If EndDate is a Saturday, Subtract 1 FROM dual. DATE_DIFF = 1. Currently I am only returning 1. Get Value for Last Month. Select * from table where date is between '01-JAN-02' and '17-JAN-02'. 242199 assumes that you want the number of solar years between 00:00 on the first date and 00:00 on the second date, to 9 significant figures. Here’s the step by step: Use the AGE function to calculate the difference between the end date and start date. @Bruno - If the data types are TIMESTAMP WITH TIME ZONE, the interval that results from subtracting the two should take care of any time zone/ daylight savings time conversion. The syntax for the DATEDIFF function is as follows:. you can check against last 90 days. (Oct 2006 - June 2005 = 16) DP_WEEK returns the distance between the weeks capturing the input. Oracle Fusion Global Human Resources Cloud Service - Version 11. If you like this article or if you have some. Adds days, months, and years to the date and returns the result. DECLARE @ADate DATETIME SET @ADate = GETDATE () SELECT DAY (EOMONTH (@ADate)) AS DaysInMonth. Let’s factor it in! First, we will take the person’s birthday and will use the DATEADD( ) function. 25 = 0. Instead, you can use simple arithmetic with Oracle dates, where subtracting one date from another gives the number of days, and where you can add an subtract days from a given date. SELECT select AVG (months_between (SYSDATE,DOB)/12) AS AVERAGE_AGE_IN_YEARS. Note that SQL Server DATEDIFF(month, date2, date1) function does not return exactly the same result, and you have to use an user-defined function if you need to fully emulate the Oracle MONTHS_BETWEEN function (see. DATEDIFF calculates the difference between two dates - it doesn't add day or months to an existing date. CREATE OR REPLACE FUNCTION datediff (p_what IN VARCHAR2, p_d1 IN DATE, p_d2 IN DATE) RETURN NUMBER /* Updated to reflect current database and PL/SQL. You could always create your own custom function in the module: Function MonthDiff (startdate, enddate) ' return number of months between two dates. Voila! You've the the last day of the month containing your reference point in time. It returns a whole month (no fraction) if both dates have the same day-of-month or. I had a problem with a date query using the operator 'between'. For ex.