Date functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
The following information describes the functions available for building expressions.
Tip:
- Any information within square brackets [ ] is optional.
- For more information on formatting strings, see Conversion Functions.
- If you want the current date/time associated with the UTC time zone, use the format CurrentDateTime("UTC").
DATE
- Description
- Returns a date value representing a specified year, month, day, hour, minute, or second. The default value for date/time is always associated with the current time zone, unless a time zone is specified using a different function in the expression. Any actions performed in the same expression using this resultant value uses the current or specified time zone for evaluation.
Note: If this function is used as the return value of an expression, the DateTime will be in universal time (UTC).
- Format
- DATE(int_year, int_month, int_day, [, int_hour, int_minute, int_second], [, timezone]])
| Parameters | Required? | Data Type | Description |
|---|
| int_year | Yes | Numeric | Integer for the year field of the date. |
| int_month | Yes | Numeric | Integer for the month field of the date. |
| int_day | Yes | Numeric | Integer for the day field of the date. |
| int_hour | No | Numeric | Integer for the hour field of the date. |
| int_minute | No | Numeric | Integer for the minute field of the date. |
| int_second | No | Numeric | Integer for the second field of the date. |
| timezone | No | String | Abbreviation of the time zone of the date. |
- Example
- DATE(2008,10,31,11,59,59)
- DATE(2008,10,31)
- DATE(2008,10,31,11,59,59,"MST")
CurrentDateTime
- Description
- Returns the current date and time. The default value for date/time is always associated with the current time zone, unless a time zone is specified using a different function in the expression. Any actions performed in the same expression using this resultant value uses the current or specified time zone for evaluation.
To work with UTC values, you can use the UTC time zone. You can do this by specifying it in a DateTime function, or by converting the DateTime value to the UTC time zone. For example, the DateTime function can be expressed as CurrentDateTime("UTC") or as ConvertTimeZone({dateTimeVariable}, "UTC".
Note: If this function is used as the return value of an expression, the DateTime will be in universal time (UTC).
- Format
- CurrentDateTime([timezone])
| Parameters | Required? | Data Type | Description |
|---|
| timezone | No | String | Time zone to get the current date and time from. |
DateTimeCompare
- Description
- Compares two Date-Time values and returns an indication of their relative values; that is, an integer that indicates whether the first instance is earlier than, the same as, or later than the second instance.
- Format
- DateTimeCompare(dateTime1, dateTime2)
| Parameters | Required? | Data Type | Description |
|---|
| dateTime1 | Yes | DateTime | First date and time value to compare. |
| dateTime2 | Yes | DateTime | Second date and time value to compare. |
- Example
- DateTimeCompare(DATE(1899,10,31,11,59,59) , DATE(1999,10,31,11,59,59) ) < 0
- DateTimeCompare(DATE(1999,10,31,11,59,59) , DATE(1999,10,31,11,59,59) ) = 0
- DateTimeCompare(DATE(1999,10,31,11,59,59) , DATE(1899,10,31,11,59,59) ) > 0
Today
- Description
- Returns the current date as a date and time value. The default value for date/time is always associated with the current time zone, unless a time zone is specified using a different function in the expression. Any actions performed in the same expression using this resultant value uses the current or specified time zone for evaluation. The time part of the day is zero (midnight).
Note: If this function is used as the return value of an expression, the DateTime will be in universal time (UTC).
- Format
- TODAY([timezone])
| Parameters | Required? | Data Type | Description |
|---|
| timezone | No | String | Time zone to get the current date from. |
Year
- Description
- Returns the year corresponding to the specified date.
Tip: The DateTime input parameter takes into account the associated time zone of the input during evaluation.
- Format
- YEAR(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date to get the year from. |
Month
- Description
- Returns a number between 1 and 12 (inclusive) representing the month of the year, corresponding to the specified date.
Tip: The DateTime input parameter takes into account the associated time zone of the input during evaluation.
- Format
- MONTH(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date to get the month from. |
Day
- Description
- Returns a number between 1 and 31 (inclusive) representing the day of the month, corresponding to the specified date.
- Format
- DAY(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date to get the day from. |
Hour
- Description
- Returns a number between 0 and 23 (inclusive) representing the hour of the day.
- Example
- Hour(CurrentDateTime("MST")) will give a result 2 off of Hour(CurrentDateTime("EST")).
- Format
- HOUR(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date to get the hour from. |
Minute
- Description
- Returns a number between 0 and 59 (inclusive) representing the minute of the hour.
Tip: The DateTime input parameter takes into account the associated time zone of the input during evaluation.
- Format
- MINUTES(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date to get the minute from. |
Second
- Description
- Returns a number between 0 and 59 (inclusive) representing the second of the minute.
- Format
- SECOND(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date to get the seconds from. |
DatePart
- Description
- Returns an integer value containing the specified component of a given DateTime value, such as the month or year.
- Format
- DatePart(component, date [, firstdayofweek [, firstweekofyear]])
| Parameters | Required? | Data Type | Description |
|---|
| component | Yes | Numeric or String | Values representing which time component to extract:- 0 or “yyyy” = year
- 1 or “q” = quarter
- 2 or “m” = month
- 3 or “y” = day of year (1 to 366)
- 4 or “d” = day of month (1 to 31)
- 5 or “ww” = week of year (1 to 53)
- 6 or “w” = day of Week (1 to 7)
- 7 or “h” = hour
- 8 or “n” = minute
- 9 or “s” = second
|
| date | Yes | DateTime | Value to extract from. |
| firstdayofweek | No | Numeric | Values indicating which day to consider as the first day of the week:- 0 = System setting
- 1 = Sunday (default)
- 2 = Monday
- 3 = Tuesday
- 4 = Wednesday
- 5 = Thursday
- 6 = Friday
- 7 = Saturday
|
| firstweekofyear | No | Numeric | Values indicating whether to consider the first week of a year to be:- 0 = System setting
- 1 = week in which Jan 1 occurs
- 2 = week that has at least 4 days in the new year
- 3 = first full week in the new year
|
DateOnly
- Description
- Gets the date component of a DateTime value, with the time zeroed out.
Tip: The DateTime input parameter takes into account the associated time zone of the input during evaluation.
- Format
- DateOnly(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date and time from which the date is isolated. |
ConvertTimeZone
- Description
- Changes the time zone associated with a DateTime value. This function does not change the specific time being referenced; that is, the universal (UTC) time remains the same. A DateTime value that is associated with the time zone is returned.
To work with UTC values, you can use the UTC time zone. You can do this by specifying it in a DateTime function, or by converting the DateTime value to the UTC time zone. For example, the DateTime function can be expressed as CurrentDateTime("UTC") or as ConvertTimeZone({dateTimeVariable}, "UTC".
- Example
- If 3:00 p.m. MST is converted to EST, then the result displayed is 5:00 p.m. EST. The time does not change, but it is associated with a different time zone.
- Format
- ConvertTimeZone(date, timeZoneID)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date and time to associate with a timezone. |
| timeZoneID | Yes | String | Represents the time zone. |
SpecifyTimeZone
- Description
- Specifies the time zone of a time value. A DateTime value that is associated with the local time is returned.
- Example
- If 3:00 p.m. MST is specified as EST, then the result is 3:00 p.m. EST. The time of the output value is the same at the input value.
- Format
- SpecifyTimeZone(date, timeZoneID)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date and time to associate with a time zone. |
| timeZoneID | Yes | String | Represents the time zone. |
GetTimeZone
- Description
- Gets the time zone the date is associated with. A string value that represents the associated time zone is returned.
- Format
- GetTimeZone(date)
| Parameters | Required? | Data Type | Description |
|---|
| date | Yes | DateTime | Date and time to get the associated time zone from. |
