The Question asked for "6 months from the system date". answered Oct 19, 2016 at 9:38. DATEDIFF (MONTH, DATEADD (DAY,-DAY (startDate)+1,startDate),DATEADD (DAY,-DAY (startDate)+1,endDate)) Share. EXECUTION_DATE, EXEC_DIFF 01-FEB-13, 1 31-JAN-13, 2 30-JAN-13, 2. 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. 679 seconds. Oracle doesn't have a DATEDIFF() function. two full prior months plus the current partial month. SYSTIMESTAMP). month: Number - The month (0-11) where 0 is January and 11 is December. 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. The system always returns a positive number regardless of. The following shows the syntax of the DATEPART() function: DATEPART ( date_part , input_date ) Code language: SQL (Structured Query Language) (sql) The DATEPART() takes two arguments: date_part is the part of a date to be extracted. 6789' SECOND (2,3) Rounded to 15. February 28 and March 31), the fractional portion is zero, even if the days of the month are not the same. To find the date difference in minutes from the given DateTime values, use the following steps: - First, find the “date_diff” in days and multiply it with “24”. This is from my own library, will return the difference of months between two dates. (Oct 10, 2006 - June 14, 2005 = 69) SELECT months_between ( date_1, date_2 )/12 years_between FROM employee. For example, in February 2014, the following returns 1, because January was the most recent calendar month:Last day of current month is actually are not 3/31/20015, it is 3/31/2015 23:59:59. 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. This question was incorrectly asked. Parameter Description; date1, date2: Required. For example, you can use this function to find the date that is 7000 minutes from today: number = 7000, datepart = minute, date = today. select distinct e1. 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 (. adddate addtime curdate current_date current_time current_timestamp curtime date datediff date_add date_format date_sub day dayname dayofmonth dayofweek dayofyear extract from_days hour last_day localtime localtimestamp makedate maketime. The functions in this section use a format string that is compatible with JodaTime’s DateTimeFormat pattern format. date_from, evnt. The Stored Procedure accepts two inputs - A start date and end date of the date range desired by the user. @WernfriedDomscheit YYYY-MM-DD is not a date it is just a formatted string. Truncate a Date to a Specific Unit of Measure. The solution takes as base the first known date by SQL Server (1900-01-01). The minus sign is used to compute the difference between two. e. February 28 and March 28) and when the days of the month are the last day of the month (e. SELECT date_part ('month',age ('2016-06-30', '2018-06-30')) The result of this query is 0. select emplid from. T was trying do calculate age as on todays date in ORACLE but after a lot of brain storming i didn't get it. 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 DUAL; Code language: SQL (Structured Query Language) (sql) The result is 6 months as shown below: with cte (start_date, end_date) as ( select date '2020-10-01', date '2020-11-02' from dual ), rcte (start_date, end_date, part_month) as ( select start_date, end_date, trunc(start_date, 'MM') from cte union all select start_date, end_date, part_month + interval '1' month from rcte where part_month < trunc(end_date, 'MM') ) select extract(month. columns c puts a limit on how many dates are returned. Multiply it by seven to give the number of weeks spanned. @KanagaveluSugumar - An Oracle DATE always has a year, month, day, hour, minute, and second component. Expressions must contain a complete hint. We’re calculating months of service in this example. Oracle常用函数:DateDiff () 返回两个日期之间的时间间隔自定义函数. This works because you have no issue with months and years or one-digit vs. 24. You'll get a more accurate result if you compute the difference between the two dates in days and divide by the mean length of a calendar year in days over a 400 year span (365. for extended XSL functions. 16. If date1. select *, cast ( (cast (begin_date as date) - cast (end_date as date) YEAR) as decimal (3,2)) AS year_diff from x. 1. + (Concatenation) operator. The math is 100% accurate for dates within a couple of hundred years or so. CASE WHEN a. CASE WHEN GETDATE () = 'first of this month' THEN 'DATE column' between 'first day of last month' and 'last day of last month'. startdate or. They both do very different things. CREATE procedure [dbo]. 1. however using the below sql statement im getting. The unit of time. 2903225806 DATE_DIFF =. DATE_ADD () Add time values (intervals) to a date value. In SQL Server, there is a function datediff with datepart 'q'/quarter which behaves as follows :--. Thus your AGE ('2019-12-01', '2018-12. DateGroup. January 30, 2004 - 7:26 pm UTC. 指定した日付の差異。次の値が有効です。 DD: 差異を日数で計算します。. Start learning SQL now ». Access Function: DateDiff ("m",PAID_DATE,STATIC_DATE) AS. ReturnDate) AS nvarchar(max)) END) AS [Duration] As a side note: I used nvarchar(max) for consistency with the first part of your query. The DATEADD() function adds or subtracts a specified time interval from a date. *Or even the CTIME. I've tried Datediff(day, start_date, end_date), but I was prompt with: invalid column name. If you want an integer. ToDate)); My SQL to LINQ Recipe might help you with some translation issues in the future. SELECT DATEADD (month, 1, '20220730'); The below DATEADD will add 1 year to the provided date value, the year changed from 2022 to 2023. LeapYear Returns one (1) if the specified year is. g. declare @datetime datetime; set @datetime = getdate (); select @datetime; select dateadd (year,datediff (year,0,@datetime),0); select dateadd (month,datediff (month,0,@datetime),0); select dateadd (day,datediff. Viewed 43k times 0 I want to. To get the start and end dates of each month within a given range, when the value of the @StartDate parameter is not the first day of the month: The first option is to truncate the @StartDate parameter to the. Parameter Description; interval: Required. Third, the more verbose method. ops$tkyte@8i> select months_between ( end_date, start_date ), 2 trunc ( months_between ( end_date, start_date ) /12 ) Years, 3 mod (. Taking out our calculator, we see that 30 minutes equals 1day/24hours*0. Return the current time. DATEDIFF(expr1,expr2) DATEDIFF() は、ある日付から別の日付までの日数の値として表現された expr1 − expr2 を返します。expr1 および expr2 は、日付または日付時間式です。 値の日付部分のみが計算に使用されます。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. 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. Here’s the syntax for the DATEADD function: DATEADD (interval, number, date); The interval. 1) date. I) if you need to calculate the elapsed time in seconds between two timestamp columns try this: SELECT extract ( day from (end_timestamp - start_timestamp) )*86400 + extract ( hour from (end_timestamp - start_timestamp) )*3600 + extract ( minute from (end_timestamp - start_timestamp) )*60 + extract ( second from (end_timestamp - start. The syntax for these extended functions is. Join our newsletter and get access to exclusive content every month. Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. The %DateAdd meta-SQL function returns a date by adding add_days to date_from. I have to get the date from the system and calculate the date difference in days. 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. Dynamic views containing Date, Time, or DateTime fields must be wrapped with the appropriate meta-SQL. Fractions are allowed; you can add 2. Query all. [proc_datediff] ( @date datetime ) as begin DECLARE @diff varchar(70) DECLARE @tmpdate datetime, @years int, @months int, @days int SELECT @tmpdate = @date SELECT @years =. 5. Modified 1 year, 8 months ago. select (dt1-dt2) * 24 * 60 * 60 from t; dt1-dt2 gives diff in days (eg: 1. *Subtract the two date values, divide by (60 seconds * 60 minutes *. then the following should be the result. Modified 8 years ago. g. This can have results that you are not expecting. Then the 1 represents the first day of that month. select S. If i change the month in the first date to Feb (02) I get something strange. {"payload":{"allShortcutsEnabled":false,"fileTree":{"inst/csv":{"items":[{"name":"jarChecksum. The date calculator calculates the difference between the 'Start Date' and 'End Date' and displays it in terms of the number of years, months, weeks and days between them. The easiest is the connect by level method: Copy code snippet. DATE_SUB () Subtract a time value (interval) from a date. 25)) AS `numberofemployees`. DECLARE @date datetime2 = '2021-01-07 14:36:17. The following table lists all the valid datepart values. I need to calculate the difference between two dates in Oracle sql and show it in the following format: 'x years y months z days'. 4193548 The number of whole months can be split into. 首先在oracle中没有datediff ()函数. 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. Consider SQL Server function to calculate the difference between 2 dates in months: SQL Server : -- Difference between Oct 02, 2011 and Jan 01, 2012 in months SELECT DATEDIFF ( month, '2011-10-02', '2012-01-01') ; -- Result: 3. Description. Month([EndDate]) - DateMonth([StartDate]) Regards, Pat29 Answers. Given two dates then the difference between them is a number of full calendar months and the remainder is the number of days (and hours, minutes and seconds) - this is easy to find using the MONTHS_BETWEEN function and some modulo arithmetic (see my. By adding 1 month, I am calculating the first day of next month and then subtraction 3 milliseconds, which allows me to arrive at the last day of the current month. 2. The expression is given to calculate the month’s difference between two dates, use the date diff expression as shown below. to get the date one second before midnight of. 2188940092. In PostgreSQL, you can take the difference in years, multiply by 12 and add. select datediff (q,'03-30-2005','04-01-2005') will return 1. This function is not sensitive to the NLS_CALENDAR session parameter. 999 is to not use the between comparison and instead use column_name >= @StartDate and column_name < @EndDate +1. select. date_open END. 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. DD is a two-digit day of the month (01 through 31). I suggest to use "months_between" function because it takes leap years into account (months_between wants 2 dates as parameters):. NEXT_QUARTERDECLARE @gapPeriod DATETIME = DATEADD(MONTH,-2,GETDATE()); --Period:Last 2 months. This works as Oracle will perform an implicit cast (using TO_TIMESTAMP( time, format_mask )) using the session parameter. 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. g. Just to make the warning explicit to everyone looking at this answer: the subquery SELECT ROW_NUMBER() OVER ( ORDER BY c. Where a. To compare dates in SQL, the most common functions used are DATEDIFF, DATE_ADD, DATE_SUB, and NOW. Factor by 24 to get hours, 24*60 to get minutes, 24*60*60 to get seconds (that's as small as dates go). SQL is a standard language for storing, manipulating and retrieving data in databases. This function is not sensitive to the NLS_CALENDAR session parameter. Again, the expected results would be a value of 1. Hi team i need to get the exact month differences between two dates i am using oracle sql for my project . Another example is to get the month of. We will be using the same employees table in order to demonstrate the intricacies of calculating the difference between two dates in terms of weeks, months, or years using MySQL’s DATEDIFF() function. DATE_DIFF = 1. Must be a date, a time, a timestamp, or an expression that can be evaluated to a date, a time, or a timestamp. 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):. Justin, the example which you gave, if that is the scenario I want the output as 1. DATE () Extract the date part of a date or datetime expression. months_between/120 = decades_between. Again, the expected results would be a value of 1. You also wouldn't prefix a function name with the @ sign. That link is not for the Oracle database, it's for JavaDB (Derby). 構文 @DATEDIFF (' difference ', ' date ', ' date ') difference. EOMONTH (date [,months to add) Returns the last do of the month with an optional parameter to add months (+ or -). 2) format. Then, it converts that result to a date, using the format 'yyyy-MM-dd'. Only 10+ years later - here's a db<>fiddle which demonstrates the difference between subtracting a date from a date (e. Gets the number of intervals between two DATE values. It counts the number of year boundaries between two dates. Oracle has a months_between function: AND MONTHS_BETWEEN (sysdate, s. mysql> SELECT. CurrentMember), Today(), DP_MONTH ) < 6 ) ON ROWS FROM Mysamp. DATEDIFF with examples DATEDIFF function accepts 3 parameters, first is datepart (can be an year, quarter, month, day, hour etc. A) Get the difference in months of dates on the same day. then if day/month of dob is less than. Assuming that a. So, we have to treat this type of cases too, which I usedMONTHS_BETWEEN = 1. where (year(getdate())-year(birthDate)) = 17. Next to calculate a month of services in a company by a user we would use ‘m’ to calculate the difference value. StartTime: 2022-27-27 14:00:00 EndTime:2022-12-12 19:30:00 Firstly, to find the time difference, we will use. Then you'll see the number. The next example will show the differences between two dates for each specific datapart and abbreviation. . So it's not an accumulative difference, it's only when a date is within a smaller month. %DateAdd . Another example using the. AccountNumber AND T2. enddate,e3. For example, suppose you have values below the start and end times. Converts one date format to a new format and returns the result. format_datetime(timestamp, format) → varchar. CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips. This command prints the order date in the specified format. Table 7-13 Date Format Models for the ROUND and TRUNC Date Functions. Thanks in advance for any help; this has been driving us crazy for a few months now. The integer value represents the day and the fractional value represents the. datediff isn't a function in Oracle. One truncates a date to the precision specified (kind of like rounding, in a way) and the other just returns a particular part of a datetime. Just to clarify SQL server seems to require DATEDIFF (datepart, recentDate, olderDate) as startdate and enddate are a bit nebulous. date_or_time_expr1, date_or_time_expr2. SQL> ed Wrote file afiedt. 8. Date > T1. Return the current time. That prevents. Oracle Fusion Application Toolkit Cloud Service - Version 11. Adds the specified number of months to a date or timestamp. For example: print &order_date () edit 'Month dd, YYYY'. Apr 5, 2021 at 15:55. MONTHS_BETWEEN = 1. I suggest to use "months_between" function because it takes leap years into account (months_between wants 2 dates as parameters): select months_between(sysdate, to_date('1994-08-13', 'YYYY-MM-DD'))/12 from dual; 26,4729751904122. And then we need to convert the output of DATEPART to a 2-digit month number. Stack OverflowThe default is zero (0). TIMESTAMPADD () manipulates data of the data types DATE and DATETIME based on a calendar year. The difference between two dates (in oracle's usual database product) is in days (which can have fractional parts). Java date functions. Viewed 3k times 0 I have a table with a date column (DD/MM/YYYY hh:mm:ss) as follows:. Improve this answer. I was given code via an SR but it doesn't work (I'm able to save the code but when I try to look at the sample data, it just says "error"). From the documentation: date_part (): The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract:Date/time functions. StartDate) AND. This function adds a specified number of days, months, and years to a given date. Use DATEADD (): where HireDate < dateadd (year, -3, GETDATE ()) DATEDIFF () does not do what you think it does. Functions. That is, this function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified start date and end date. This function is used to get the timestamp value from the specified character string. The number is the number of seconds elapsed since midnight, January 1, 1970. Add a number of months (n) to a date and return the same day which is n of months away. 3. datediff() is not giving tHe result as I am working in Oracle. DATEDIFF() Returns the difference between the two dates. NEXT_DAY(date,weekday) Code language: SQL (Structured Query Language) (sql) Arguments. Here is an example that uses date functions. 'month' or month). (date1 - date2)The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. ROUND returns date rounded to the unit specified by the format model fmt. (Oct 2006 - June 2005 = 16) DP_WEEK returns the distance between the weeks capturing the input dates. The syntax for DATEDIFF is pretty straightforward: DATEDIFF (datepart, startdate, enddate) Let’s explore the parameters used here: datepart: The unit of time you want to use for the calculation, like year, quarter, month, day, or even smaller units like hour, minute, or second. 0. DATEADD ('week', 1, [due date]) Add 280 days to the date February 20, 2021. The add_days variable can be negative. Any help would be appreciated. DAYS function. Tip. Share. 天：. It operates according to the rules of the Gregorian calendar. Use a built-in function to “move”. Property)) STDEV(Property) EF Core 7. From the inputs you got there are 123 months between the date of 07/03/2011 to 24/3/2021. DATEDIFF (MONTH, DATEADD (DAY,-DAY (startDate)+1,startDate),DATEADD (DAY,-DAY (startDate)+1,endDate)) Share. In SQL Server, there is a function datediff with datepart 'q'/quarter which behaves as follows :--. Share. We would like to show you a description here but the site won’t allow us. In this article, we learned how to do SQL subtract dates using the DATEDIFF function. (CASE WHEN Trips. Oracle PeopleSoft Tips and Tricks. Just divide the total by 7 to get the weeks and then calculate the remaining days. Modified 1 year, 8 months ago. Dateadd function is not working in oracle SQL. The month and the last day of the month are defined by the parameter NLS_CALENDAR. SELECT DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))Time Calculator. dividing by 365. StartTime: 2022-27-27 14:00:00 EndTime:2022-12-12 19:30:00 Firstly, to find the time difference, we will use. If IsDate (startdate) and IsDate (enddate) Then. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed, or activated on delivered hardware, and modifications of such programs), ii) Oracle computer. 1. Returns. 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 crossed once. 2323 days) multiply by 24 = hours, another 60 = minutes, another 60 = seconds. g. As we can see in the screen shot the query displays the months between the two dates. For example, suppose you have values below the start and end times. This post has been answered by 630199 on Jun 1 2009. Another example using the MONTHS_BETWEEN function in Oracle/PLSQL is: MONTHS_BETWEEN (TO_DATE ('2003/07/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') ) would return 3. Apr 2, 2013 at 6:13. 0. In my opinion the date diff is kind of subjective, especially on days. Oracle equivalent to SQL Server/Sybase. 5 Answers Sorted by: 65 I'd use months_between, possibly combined with floor: select floor (months_between (date '2012-10-10', date '2011-10-10') /12) from dual;. It uses month boundaries as calculating the difference in months, with each change in calendar month adding one to the answer. In the ORDER BY clause, you want to order by the actual date. February 28 and March 31) can lead to unintuitive behavior; specifically, increasing the first date in the pair does not always increase the output value. When you subtract two dates in Oracle, you get the number of days between the two values. Concatenates a date to a time on either side of the + symbol and returns a TIMESTAMP or TIMESTAMPTZ. , NextDate, DATEDIFF("D", Date, NextDate) FROM ( SELECT ID, AccountNumber, Date, ( SELECT MIN(Date) FROM YourTable T2 WHERE T2. Viewed 44k times 3 I've got 2 date columns in my table (start_date, end_date). In SQL Server, it could be something like this: DELETE FROM table WHERE DATEDIFF(month, load_timestamp, GETDATE()) > 13; It would be slightly different in each database – not all of them have a DATEDIFF function. Note: This answer applies to dates represented by the Oracle data type DATE . The application passes in two parameters: a string representing the number of the desired month (i. Modified 1 year, 3 months ago. Admission_Date < ADD_MONTHS (sysdate, -5) which could still use an index on that column, if there is one. 58064516129032. add_months (date,n) Returns the date that corresponds to date plus the number of months indicated by the integer n . 0. For a complete reference to SQL expressions, go to the Oracle website (requires Oracle account activation). We are in the process of migrating to a PostgreSQL SDE and with the same feature class the query no. 32 illustrates the behaviors of the basic arithmetic operators ( +, *, etc. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a case statement that decrements the result. From the inputs you got there are 123 months between the date of 07/03/2011 to 24/3/2021. And determining if the date falls on a holiday (by querying against your holiday table) should be trivial. Oracle-compatible TO_CHAR function that can format a timestamp, a number. The screenshot below shows the data I want as well as the incorrect month between results I am obtaining. Must be one of the values listed in Supported Date and Time Parts (e. DatePart. SQL Query with Dates. 000 which is the start of the. CREATE FUNCTION trunc_date (@date DATETIME) RETURNS DATETIME AS BEGIN SELECT CONVERT (varchar, @date,112) END. DATE_FROM_UNIX_DATE. It is easiest to use DATEDIFF with MONTH on the DAX side, but in Power Query you can use the formula below. Constructs a DATE value. Last 3 Months. Then Oracle will not use an index on the date_column and would need a separate function-based index on either TRUNC(date_column) or TO_CHAR(date_column, 'DD-MM-YYYY'). 005479 0. How To turn a string with "pipe-separated" values into individual rows in Oracle PL/SQL. We will be using the same employees table in order to demonstrate the intricacies of calculating the difference between two dates in terms of weeks, months, or years using MySQL’s DATEDIFF() function. select (sysdate +1 - rownum) dt from dual connect by rownum <= (sysdate - add_months (sysdate - extract (day from sysdate),-2)); The "-2" is the number of prior full months of dates to include. Below is a syntax for the DATEDIFF () function. Example. DATE is the main – or rather, original – datatype used in Oracle for holding dates. Function. DATEDIFF( date_part , start_date , end_date) Code language: SQL (Structured Query Language) (sql) The DATEDIFF() function accepts three arguments: date_part, start_date, and end_date. S is milliseconds in one or up to six digits. DATE_FORMAT(): This function formats a specified date as per the format specifier (%e for date, %c for month, %Y for year, and many more). 2. This function adds N months to a date and returns the same day N month after. To answer your question to find all records that occurred last month. The datepart value cannot be specified in a variable, nor as a quoted string like 'month'. So, the difference between Jan 1 20015 and Dec 31 2016 is 1 year. SQL DATEPART. If either expression is a character or graphic string, it must not be a CLOB. So if the timeframe is longer than a working week (five days) you can: Find the number of weeks by taking the floor of the N / 5. We learned with examples, how to get information. DateDiffDay (r. You could always create your own custom function in the module: Function MonthDiff (startdate, enddate) ' return number of months between two dates. The week (wk, ww) datepart reflects changes made to SET DATEFIRST. 14. 25) This seems to be another common approach - and was also given in a comment on my question. It should have been DATEDIFF NOT DATEADD with respect to the WEEK parameter. Returns the <date> with the specified number <interval> added to the specified <date_part> of that date. 2. 5 days to a given date. You may: count the number of 24h elapsed time: day+1 - day = 1 day = 24h. Syntax. date + time. Think of any strings that are inside the range '9/30/2016 14:00:00' to '9/30/2016 17:00:00'. You can use the DateDiff function to determine how many specified time intervals exist between two dates. %DateDiff – returns the number of days between two date values. 2903225806. INTERVAL '15. For a baby born 28-Feb-17, properly calculated they turn 1 years old on 28-Feb-18 - their next birthday. The name of the order date month is printed, followed by the day of the month, a comma, and four-digit year. Thanks for the infoFirst step is to join the tables together such that we get a row for every relevant month that intersects with the start and end dates in your HRAL table. Previous SQL Server Functions Next . 1. Syntax. Which leads me to believe that it is treating February. DECLARE @ADate DATETIME SET @ADate = GETDATE () SELECT DAY (EOMONTH (@ADate)) AS DaysInMonth. The MDX DateDiff function for Essbase returns the difference (a number) between two input dates in terms of the specified date-parts, following a standard Gregorian calendar. COMPUTE days2 = DATEDIFF(date2,date1,"days"). Just divide the total by 7 to get the weeks and then calculate the remaining days. Syntax: TIMESTAMPADD (interval, expr, timestamp) Example:. The DATEDIFF function does not calculate the difference in months based on days. If date1 is later than date2 , then the result is positive. The format argument is a string format that specifies which unit the date should be rounded to.