Date and time functions
The list below shows date and time functions that can be used in expressions.
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
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
Valid arguments for Arg1 are: ' ' ' ' ' ' ' ' ' ' ' Examples:
|
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
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: ' ' ' ' ' ' ' ' ' ' ' ' ' ' Example:
For examples, see Calculations of time differences and Calculating time periods. |
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. The underlying data of the
calculation is an
Integer , but regional settings determine the
formatted output for some of the arguments ('mm ',
'qq ', 'dw ').
Valid arguments for Arg1 are: ' ' ' ' ' ' ' ' ' ' ' ' ' ' Example:
|
DateTimeNow()
|
Returns the current system time.
Note that when running the expression in a web client or using Automation Services, it is the time in the time zone of the service (on the server) that is returned. Example:
For examples, see Calculations of time differences and Calculating time periods. |
Day(Arg1)
|
Extracts the day of month from a
Date or
DateTime column. The result is an integer
between 1 and 31.
Example:
|
DayOfMonth(Arg1)
|
Extracts the day of month from a
Date or
DateTime column. The result is an integer
between 1 and 31.
Example:
|
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 (Sunday) and 6 (Saturday), but regional settings
determine the start of the week as well as the formatted output.
Example:
|
DayOfYear(Arg1)
|
Extracts the day of year for a
Date or
DateTime column. Returns an integer between 1
and 366.
Example:
|
Days(Arg1)
|
Returns the number of days for a
TimeSpan as an
Integer value between -10675199 and 10675199;
or, if the argument is an
Examples:
|
FiscalMonth(Arg1,
Arg2)
|
Returns the fiscal month for a date.
The first argument is the
If no second argument is specified then the value of the
document property
When used in hierarchies, the
The hierarchy in the image is built using the following expression:
Examples:
|
FiscalQuarter
(Arg1,
Arg2)
|
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
Examples:
|
FiscalYear (Arg1,
Arg2)
|
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
Examples:
|
Hour(Arg1)
|
Extracts the hour from a
DateTime or
Time column. Returns an integer between 0 and
23.
Example:
|
Hours(Arg1)
|
Returns the number of hours for a
TimeSpan as an
Integer value between 0 and 23;
or, if the argument is an
Examples:
|
ISOWeek(Arg1)
|
Returns the ISO week according to ISO 8601
for a date (Date or DateTime) as an integer between 1 and 53.
Example:
|
ISOYear(Arg1)
|
Returns the ISO week-numbering year
according to ISO 8601 from a date (Date or DateTime) as an integer.
Example:
|
Millisecond(Arg1)
|
Extracts the millisecond from a
DateTime or
Time column. Returns an integer between 0 and
999.
Example:
|
Milliseconds(Arg1)
|
Returns the number of milliseconds for a
TimeSpan as an
Integer value between 0 and 999;
or, if the argument is a
Examples:
|
Minute(Arg1)
|
Extracts the minute from a
DateTime or
Time column. Returns an integer between 0 and
59.
Example:
|
Minutes(Arg1)
|
Returns the number of minutes for a
TimeSpan as an
Integer value between 0 and 59;
or, if the argument is an
Examples:
|
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:
|
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:
|
Second(Arg1)
|
Extracts the second from a
DateTime or
Time column. Returns an integer between 0 and
59.
Example:
|
Seconds(Arg1)
|
Returns the number of seconds for a
TimeSpan as an
Integer value between 0 and 59;
or, if the argument is an
Examples:
|
Today()
|
Returns the current date.
Note that when running the expression in a web client or using Automation Services, it is the date in the time zone of the service (on the server) that is returned. Example:
|
TotalDays(Arg1)
|
Returns the number of days for a
TimeSpan as a real value expressed in whole days
and fractional days.
Example:
|
TotalHours(Arg1)
|
Returns the number of hours for a
TimeSpan as a real value expressed in whole and
fractional hours.
Example:
|
TotalMilliseconds(Arg1)
|
Returns the number of milliseconds for a
TimeSpan as a real value expressed in whole and
fractional milliseconds.
Example:
|
TotalMinutes(Arg1)
|
Returns the number of minutes for a
TimeSpan as a real value expressed in whole and
fractional minutes.
Example:
|
TotalSeconds()
|
Returns the number of seconds for a
TimeSpan as a real value expressed in whole and
fractional seconds.
Example:
|
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. See also
ISOWeek.
Example:
|
Year(Arg1)
|
Extracts the year from a
Date or
DateTime column. The result is of type
Integer . See also ISOYear.
Example:
|
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. The calculation depends on regional
settings, and might produce undesired results when crossing year boundaries.
See also
ISOYear ,
ISOWeek , and
BinByDateTime.
Example:
For more information, see Additional operations with time hierarchies. |