MicroStrategy ONE
DateDiff
DateDiff is used to calculate the length of time between two dates. A numeric value is returned.
Syntax
DateDiff<firstWeekDay>(Date1, Date2, Unit)
Where:
Date1andDate2are the inputs used for date or timestamp values. You can use metrics, constants, attribute forms, or functions that result in a date or timestamp. For example, you can include CurrentDate() as an input to compare historical dates from an attribute form to the current date.Unitis the unit of time that is to be measured. You can return the length of time in one of the following units:
| Unit of time | Value in the function expression |
|
Seconds |
"s" |
|
Minutes |
"mn" |
|
Hours |
"h" |
|
Days |
"d" |
|
Weeks |
"w" |
|
Months |
"m" |
|
Quarters |
"q" |
|
Years |
"y" |
firstWeekDay(default value is 1) is a parameter that can be used if you are returning the length of time in weeks. This parameter determines which day of the week is considered as the first day of the week, so that the difference in weeks can be accurately determined. You can type an integer value from 1 to 7, with 1 representing Sunday, 2 representing Monday, and so on until 7 representing Saturday.
For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.
Example
You can use DateDiff to create a metric or attribute form that lists the length of time between two dates. One common way to do this is to compare the date information you have stored to the current date. Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:
DateDiff(Day@ID, CurrentDate(), "d")
For each element of the Day attribute, the metric displays the number of days between that date and the current date when running the report or dashboard.
When using the DateDiff function in MicroStrategy Web, you will need to replace the Day@ID attribute in the function definition. Create the metric [MAX(Day@ID)] and define the function as follows: DateDiff([MAX(Day@ID)], CurrentDate(), "d")