Date Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
DateAdd
Description: Returns a date to which a specified time interval has been added.
Syntax: DateAdd(Interval, Number, Date)
Interval codes:
'yyyy'— year
'q'— quarter
'm'— month
'd'— day
'h'— hour
'n'— minute
's'— second
Input: Interval string, integer to add (negative for subtraction), date expression
Output: Resulting date value
Business Use Case: Calculate the date 3 months from the current period for forward-looking reports.
WITH MEMBER [Measures].[Future Date] AS
DateAdd('m', 3, CDate([Date].[Calendar].CurrentMember.Name)) SELECT
[Measures].[Future Date] ON COLUMNS, [Date].[Calendar].[Month].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]DateDiff
Description: Returns the difference between two dates in a specified time interval.
Syntax: DateDiff(Interval, Date1, Date2)
Input: Interval string, first date, second date
Output: Numeric difference in the specified interval
Business Use Case: Calculate the number of days since an order date.
WITH MEMBER [Measures].[Days Since Order] AS DateDiff('d',
CDate([Date].[Calendar].CurrentMember.Name), Now())
SELECT
[Measures].[Days Since Order] ON COLUMNS, [Date].[Calendar].[Date].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Month].[January 2012]CDate
Description: Converts a string expression to a date value.
Syntax: CDate(String_Expression)
Input: A string representing a valid date
Output: Date value
Business Use Case: Convert member name strings to date values for use in DateAdd/DateDiff calculations.
WITH MEMBER [Measures].[Date Value] AS CDate([Date].[Calendar].CurrentMember.Name)
SELECT
[Measures].[Date Value] ON COLUMNS, [Date].[Calendar].[Month].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Now
Description: Returns the current date and time.
Syntax: Now()
Input: None Output: Current datetime value
Business Use Case: Calculate how many days old each order period is.
WITH MEMBER [Measures].[Period Age Days] AS
DateDiff('d', CDate([Date].[Calendar].CurrentMember.Name), Now()) SELECT
{[Measures].[Internet Sales Amount], [Measures].[Period Age Days]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].Members ON ROWS FROM [Adventure Works]YEAR
Description: Returns the year component of a date value.
Syntax: YEAR(Date_Expression)
Input: A date expression
Output: Integer year number
Business Use Case: Extract the year from a member name for year-based calculations.
WITH MEMBER [Measures].[Year Number] AS YEAR(CDate([Date].[Calendar].CurrentMember.Name))
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Year Number]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]MONTH
Description: Returns the month component (1-12) of a date value.
Syntax: MONTH(Date_Expression)
Input: A date expression
Output: Integer month number (1-12)
Business Use Case: Extract the month number to apply month-specific multipliers.
WITH MEMBER [Measures].[Month Number] AS MONTH(CDate([Date].[Calendar].CurrentMember.Name))
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Month Number]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]GetDate
Description: Kyvos custom function. Returns the current date (without time component).
Syntax: GetDate()
Input: None
Output: Current date
WITH MEMBER [Measures].[Today] AS GetDate()
SELECT
[Measures].[Today] ON COLUMNS
FROM [Adventure Works]GetTimestamp
Description: Kyvos custom function. Returns the current date and time as a timestamp.
Syntax: GetTimestamp()
Input: None
Output: Current datetime timestamp
WITH MEMBER [Measures].[Report Timestamp] AS GetTimestamp()
SELECT
[Measures].[Report Timestamp] ON COLUMNS
FROM [Adventure Works]