Function
Description
DateAdd(Arg1, Arg2, (Arg3))
Adds an interval to a Date, Time or a DateTime. The method can add either a TimeSpan or an integer representing a specified date or time part (e.g., a number of days).
If a TimeSpan is to be added, two arguments are needed: a DateTime column and a TimeSpan column.
If an integer value is to be added to a date or time part, three arguments are used: Arg1 is a string describing which part to add. Arg2 is a number which contains the number of parts to add. Arg3 is the Date, Time or DateTime column.
Valid arguments for Arg1 are:
'year' or 'yy' - The year.
'quarter' or 'qq' - The quarter.
'month' or 'mm' - The month.
'day' or 'dd' - The day.
'week' or 'wk' - The week.
'hour' or 'hh' - The hour.
'minute' or 'mi' - The minute.
'second' or 'ss' - The second.
'millisecond' or 'ms' - The millisecond.
Examples:
DateAdd([Date Column], [TimeSpan Column])
DateAdd('year', 2, [Date Column])
DateAdd('month', 1, [Date Column])
DateDiff(Arg1, Arg2, (Arg3))
Calculates the difference between two Date, Time or DateTime columns. The result is presented either as a TimeSpan or as a real value representing a specified time part (e.g., number of days).
If two arguments are used (a start date column and a stop date column) the result will be a TimeSpan value displaying the total difference.
If three arguments are used, the first argument should be the part to compare. The second argument is the start date column and the third argument is the stop date column. The result of the operation is a real value.
Valid arguments for Arg1 are:
'year' or 'yy' - The year.
'quarter' or 'qq' - The quarter.
'month' or 'mm' - The month.
'day' or 'dd' - The day.
'week' or 'wk' - The week.
'hour' or 'hh' - The hour.
'minute' or 'mi' - The minute.
'second' or 'ss' - The second.
'millisecond' or 'ms' - The millisecond.
Example:
DateDiff([Order Date], [Delivery Date])
DateDiff('day', [Order Date], [Delivery Date])
DatePart(Arg1, Arg2)
Returns a specified part of a Date, Time or DateTime. Arg1 is a string describing which part of the date to get and Arg2 is the Date, Time or DateTime column.
Valid arguments for Arg1 are:
'year' or 'yy' - The year.
'quarter' or 'qq' - The quarter.
'month' or 'mm' - The month.
'day of year' or 'dy' - The day of year.
'day' or 'dd' - The day.
'year and week' or 'yywk' - The year and week.
'week' or 'wk' - The week.
'day of week' or 'dw' - The weekday.
'hour' or 'hh' - The hour.
'minute' or 'mi' - The minute.
'second' or 'ss' - The second.
'millisecond' or 'ms' - The millisecond.
Example:
DatePart('year', [Date Column])
DateTimeNow()
Returns the current system time.
Example:
DateTimeNow()
Day(Arg1)
Extracts the day of month from a Date or DateTime column. The result is an integer between 1 and 31.
Example:
Day([Date Column])
DayOfMonth(Arg1)
Extracts the day of month from a Date or DateTime column. The result is an integer between 1 and 31.
Example:
DayOfMonth([Date Column])
DayOfWeek(Arg1)
Extracts the day of week from a Date or DateTime column. The underlying data of the new column is an integer between 0 and 6, but regional settings determine the start of week as well as the formatted output.
Example:
DayOfWeek([Date Column])
DayOfYear(Arg1)
Extracts the day of year for a Date or DateTime column. Returns an integer between 1 and 366.
Example:
DayOfYear([Date Column])
Days(Arg1)
Returns the number of days for a TimeSpan as an integer between -10675199 and 10675199.
Example:
Days([TimeSpan Column])
FiscalMonth
Returns the fiscal month for a date.
The first argument is the Date column. The second argument (optional) is the number of months to shift.
If no second argument is specified then the value of the document property FiscalYearOffset will be used. Regional settings determine the formatted output.
When used in hierarchies, the FiscalMonth method keeps track of the sort order of the months so that visualization items are shown in the correct order. For example, if the fiscal year ends with January, then the next fiscal year should start with February as shown below and not with January:
The hierarchy in the image is built using the following expression:
<FiscalYear([Date Column],3) NEST FiscalMonth([Date Column],3)>
Examples:
FiscalMonth([Date Column], 1)
FiscalMonth([Date Column])
FiscalQuarter
Returns the fiscal quarter for a date (Arg1), based on a shift specified in months (Arg2).
The second argument is optional. If no second argument is specified then the value of the document property FiscalYearOffset will be used.
Examples:
FiscalQuarter([Date Column], 1)
FiscalQuarter([Date Column])
FiscalYear
Returns the fiscal year for a date (Arg1), based on a shift specified in months (Arg2).
The second argument is optional. If no second argument is specified then the value of the document property FiscalYearOffset will be used.
Examples:
FiscalYear([Date Column], 1)
FiscalYear([Date Column])
Hour(Arg1)
Extracts the hour from a DateTime or Time column. Returns an integer between 0 and 23.
Example:
Hour([Time Column])
Hours(Arg1)
Returns the number of hours for a TimeSpan as an integer between 0 and 23.
Example:
Hours([TimeSpan Column])
Millisecond(Arg1)
Extracts the millisecond from a DateTime or Time column. Returns an integer between 0 and 999.
Example:
Millisecond([Time Column])
Milliseconds(Arg1)
Returns the number of milliseconds for a TimeSpan as a real value between 0.0 and 999.0.
Minute(Arg1)
Extracts the minute from a DateTime or Time column. Returns an integer between 0 and 59.
Example:
Minute([Time Column])
Minutes(Arg1)
Returns the number of minutes for a TimeSpan as an integer between 0 and 59.
Example:
Minutes([TimeSpan Column])
Month(Arg1)
Extracts the month from a Date or DateTime column. The underlying data of the new column is an integer between 1 and 12, but regional settings determine the formatted output.
Example:
Month([Date Column])
Quarter(Arg1)
Extracts the quarter from a Date or DateTime column. The underlying data of the new column is an integer between 1 and 4, but regional settings determine the formatted output.
Example:
Quarter([Date Column])
Second(Arg1)
Extracts the second from a DateTime or Time column. Returns an integer between 0 and 59.
Example:
Second([Time Column])
Seconds(Arg1)
Returns the number of seconds for a TimeSpan as an integer between 0 and 59.
Example:
Seconds([TimeSpan Column])
TotalDays(Arg1)
Returns the number of days for a TimeSpan as a real value expressed in whole days and fractional days.
Example:
TotalDays([TimeSpan Column])
TotalHours(Arg1)
Returns the number of hours for a TimeSpan as a real value expressed in whole and fractional hours.
Example:
TotalHours([TimeSpan Column])
TotalMilliseconds(Arg1)
Returns the number of milliseconds for a TimeSpan as a real value expressed in whole and fractional milliseconds.
Example:
TotalMilliseconds([TimeSpan Column])
TotalMinutes(Arg1)
Returns the number of minutes for a TimeSpan as a real value expressed in whole and fractional minutes.
Example:
TotalMinutes([TimeSpan Column])
TotalSeconds()
Returns the number of seconds for a TimeSpan as a real value expressed in whole and fractional seconds.
Example:
TotalSeconds([TimeSpan Column])
Week(Arg1)
Extracts the week from a Date or DateTime column as an integer between 1 and 54, where the first week of year is dependent on the regional settings.
Example:
Week([Date Column])
Year(Arg1)
Extracts the year from a Date or DateTime column. The result is of type Integer.
Example:
Year([Date Column])
YearAndWeek(Arg1)
Extracts the year and week from a Date or DateTime column. Returns an integer (Year*100 + Week number), for example, the date 2005-10-13 will return 200541.
Example:
YearAndWeek([Date Column])