Page tree

 

Support | BlogContact Us | 844.332.2821

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

D toc

 

Excerpt

Calculates the difference between two valid date values for the specified units of measure.

  • Inputs must be column references.
  • The first value is used as the baseline to compare the date values.
  • Results are calculated to the integer value that is closest to and lower than the exact total; remaining decimal values are dropped.

D s
snippetBasic

D code

derive value:DATEDIF(StartDate, EndDate, month)

Output: Generates a column of values calculating the number of full months that have elapsed between StartDate and EndDate

D s
snippetSyntax

D code

derive value:DATEDIF(date1,date2,date_units )

ArgumentRequired?Data TypeDescription
date1YdatetimeStarting date to compare
date2YdatetimeEnding date to compare
date_unitsYstringString literal representing the date units to use in the comparison

D s lang notes

date1, date2

Date values to compare using the date_units units. If date2 > date1, then results are positive.

  • Date values must be column references.
  • If date1 and date2 have a specified time zone offset, DATEDIF calculates the difference including the timezone offsets
  • If date1 does not have a specified time zone but date2 does, DATEDIF uses the local time in the same time zone as date2 to calculate the difference. DATEDIF returns the difference without the time zone offset.

D s
snippetusage

 

Required?Data TypeExample Value
YesString (Date column reference)LastContactDate

date_units

Unit of date measurement to calculate between the two valid dates.

D s
snippetusage

Required?Data TypeExample Value
YesStringyear

Accepted Value for date units:

  • year
  • quarter
  • month
  • dayofyear

  • week
  • day
  • hour
  • minute
  • second
  • millisecond

D s
snippetExamples

Example - aged orders

Include Page
EXAMPLE - DATEDIF Function
EXAMPLE - DATEDIF Function

Example - dayofyear Calculations

This example demonstrates how dayofyear is calculated using the DATEDIF function, specifically how leap years and leap days are handled. Below, you can see some example dates. The year 2012 was a leap year. 

Source:

dateIdd1d2Notes
11/1/1010/10/10Same year; no leap year
21/1/1010/10/11Different years; no leap year
310/10/111/1/10Reverse dates of previous row
42/28/114/1/11Same year; no leap year;
52/28/124/1/12Same year; leap year; spans leap day
62/29/124/1/12Same year; leap year; d1 = leap day
72/28/112/29/12Diff years; d2 = leap day; converted to March 1 in d1 year

Transform:

In this case, the transform is simple:

D code

derive value:DATEDIF(d1,d2,dayofyear) as:'datedifs'

Results:

dateIdd1d2datedifsNotes
11/1/1010/10/10282Same year; no leap year
21/1/1010/10/11282Different years; no leap year
310/10/111/1/10-282Reverse dates of previous row
42/28/114/1/1132Same year; no leap year;
52/28/124/1/1233Same year; leap year; spans leap day
62/29/124/1/1232Same year; leap year; d1 = leap day
72/28/112/29/121Diff years; d2 = leap day; converted to March 1 in d1 year

Rows 1 - 3:

  • Row 1 provides the baseline calc.
  • In Row 2, the same days of the year are used, but the year is different by a count of 1. However, since we are computing dayofyear the result is the same as for Row 1.

    Info

    NOTE: When computing dayofyear, the year value for d2 is converted to the year of d1. The difference is then computed.

  • Row 3 represents the reversal of dates in Row 2.

    Info

    NOTE: Negative values for a dayofyear calculation indicate that d2 occurs earlier in the calendar than d1, ignoring year.

Rows 4 - 7: Leap years

  • Row 4 provides a baseline calculation for a non-leap year.
  • Row 5 uses the same days of year as Row 4, but the year (2012) is a leap year. Dates span a leap date (February 29). Note that the DATEDIF result is 1 more than the value in the previous row. 

    Info

    NOTE: When the two dates span a leap date and the year for d1 is a leap year, then February 29 is included as part of the calculated result.

  • Row 6 moves date 1 forward by one day, so it is now on a leap day date. Result is one less than the previous row, which also spanned leap date. 
  • Row 7 switches the leap date to d2. In this case, d2 is converted to the year of d1. However, since it was a leap day originally, in the year of d1, this value is March 1. Thus, the difference between the two dates is 1.

    Info

    NOTE: If d2 is a leap date and the year for d1 is not a leap year, the date used in for d2 is March 1 in the year of d1.

D s also
labeldate