DateTime functions
Overview
Data Management's DateTime functions extract and format DateTime information, return the current DateTime, and calculate DateTime-based values.
AsDateTime
Attempts to interpret value as a DateTime value.
Syntax
AsDateTime( value )
The required argument value may be of type Integer, Float, Date, Time, or DateTime.
Remarks
If value is an Integer or Float, then value is interpreted as a "Julian" value (seconds since Jan 1, 1600). If value is a Date, then the DateTime is initialized to that Date at midnight. If value is a Time, then the DateTime is initialized to that time on Jan 1, 1600.
Examples
AsDateTime(CurrentDate())
returns a DateTime value containing the current date, with the current time set to midnight.
ConvertTimezone
Given the date_time value that is assumed to be in the tzFrom timezone, convert it to the equivalent DateTime as represented in the tzTo timezone.
Syntax
ConvertTimezone( date_time, tzFrom, tzTo )
The ConvertTimezone function syntax has the following arguments:
Part | Description |
---|---|
date_time | Required. A DateTime value, presumed to exist in the from_tz timezone. |
tzFrom | Required. A text value containing the name or abbreviation of the original timezone. If empty, this is interpreted as the Data Management server's local timezone. |
tzTo | Required. A text value containing the name or abbreviation of the new timezone. If empty, this is interpreted as the Data Management server's local timezone. |
Remarks
This function attempts to recognize timezone abbreviations (such as "MST" or "MDT" instead of "America/Denver"). However, these variants are ambiguous and should be avoided. View a list of supported timezone names.
Example
To convert a DateTime from 8 A.M. Denver time to UTC, this expression returns 3 P.M. on Jan 3rd, 2015.
ConvertTimezone(
ScanDateTime("2015-01-03 08:00:00", "yyyy-MM-dd HH:mm:ss"), "America/Denver", "UTC"
)
CurrentDateTime
Returns a DateTime that is the current date and system time.
Syntax
CurrentDateTime( )
Remarks
The returned DateTime value is of the form "16:43:46 10 Feb 2020", so if you format it directly using AsText(), you'll get this format. If you assign a DateTime to a field, it will take on the format of the field.
Examples
Year(Date(CurrentDateTime()))
returns the current year.CurrentDateTime() -- DATETIME
returns the number of seconds elapsed since DATETIME (assumed to be a field of type Date). Note that you don't need to use Julian() to subtract DateTimes.
Date
Returns the Date portion of the DateTime
Syntax
Date( date_time )
The required argument date_time must be of type DateTime.
Example
Date(DATETIME) = CurrentDate()
tests to see if the date portion of a DateTime field is equal to the current date.
DateTimeToUnixEpochMillis
Converts a DateTime to Unix epoch time, expressed in milliseconds.
Syntax
DateTimeToUnixEpochMillis( datetime )
DateTimeToUnixEpochMillis( datetime, timezone )
Part | Description |
---|---|
datetime | Required. A DateTime value. |
timezone | Optional. A text value containing the name or abbreviation of the original timezone. |
Remarks
This function optionally attempts to recognize timezone abbreviations (such as "MST" or "MDT" instead of "America/Denver"). However, these are ambiguous and should be avoided. View a list of supported timezone names.
Example
DateTimeToUnixEpochMillis(CurrentDateTime(),"GMT")
returns the current Unix DateTime in the Greenwich Mean Time time zone.
DateTimeToWindowsEpochTicks
Converts a DateTime to Windows epoch time, expressed in 100-nanosecond ticks.
Syntax
DateTimeToWindowsEpochTicks( datetime )
DateTimeToWindowsEpochTicks( datetime, timezone )
Part | Description |
---|---|
datetime | Required. A DateTime value. |
timezone | Optional. A text value containing the name or abbreviation of the original timezone. |
Remarks
This function optionally attempts to recognize timezone abbreviations (such as "MST" or "MDT" instead of "America/Denver"). However, these are ambiguous and should be avoided. View a list of supported timezone names.
Example
DateTimeToWindowsEpochTicks(CurrentDateTime(),"GMT")
returns the current Windows DateTime in the Greenwich Mean Time time zone.
FormatDateTime
Formats date_time, using the specified format_string, and returns a text value.
Syntax
FormatDateTime( date_time, format_string )
See Configuring Date, Time, and Date-Time data types for details on format strings.
Julian (dateTime)
Returns a Float that is the difference, in seconds, between date_time and midnight on Jan 1, 1600.
Syntax
Julian( date_time )
The required argument date_time must be of type DateTime.
Remarks
You don't need to use Julian to find the difference between two DateTime values. Subtract them and the result will be in seconds. For example, to find the number of seconds elapsed since midnight Jan 1, 1900 use CurrentDateTime() -- AsDateTime(MakeDate(1, 1, 1900))
Example
Julian(CurrentDateTime())
returns the number of seconds elapsed since Jan 1, 1600.
MakeDateTime
Constructs a DateTime from Date and Time components.
Syntax
MakeDateTime( date, time )
The AsDateTime function syntax has the following arguments:
Part | Description |
---|---|
date | Required. Must be of type Date. |
time | Required. Must be of type Time or DateTime. |
Remarks
This is useful when building a DateTime out of discrete components.
Examples
MakeDateTime(CurrentDate() + 1, MakeTime(17, 0, 0))
constructs a DateTime that is tomorrow at 5pm.
ScanDateTime
Uses the specified format_string to parse date_time and return a DateTime.
Syntax
ScanDate( date_time, format_string )
Part | Description |
---|---|
date_time | Required. A date-time value. |
format_string | Required. See Configuring Date, Time, and Date-Time data types for details on format strings. |
Remarks
If the ScanDateTime function encounters an erroneous DateTime value like 2016-01-01 25:30:00, it will "roll over" the returned DateTime to the correct DateTime (in this example, 01:30:00 02 Jan 2016). If you'd rather treat such inputs as errors, use the function StrictScanDateTime instead. Note that the function StrictScanDateTime uses a somewhat different set of format strings
Example
ScanDateTime( "2016-01-01 23:30:00", "YYYY-DD-MM HH:mm:ss")
returns 23:30:00 01 Jan 2016.
StrictScanDateTime
Uses the specified format_string to check date_time for validity and returns the DateTime if valid.
Syntax
StrictScanDate( date_time, format_string )
Part | Description |
---|---|
date_time | Required. A date-time value. |
format_string | Required. Note that the required format_string must use the Strict DateTime format syntax rather than the DateTime format symbols used by other functions and tools. |
Remarks
The StrictScanDateTime function enforces the specified format_string when parsing date and time from a string into a DateTime. If the function encounters bad formatting, or an erroneous DateTime value like 2016-01-01 25:30:00, it returns an error. If you'd prefer to "roll over" such values to the correct DateTime, use the function ScanDateTime instead.
Examples
StrictScanDateTime( "2016-01-01 23:30:00", "YYYY-dd-MM HH:mm:ss")
returns 23:30:00 01 Jan 2016.StrictScanDateTime( "2016-01-01 25:30:00", "YYYY-dd-MM HH:mm:ss")
returns an error.
Date-time value | format_string pattern |
---|---|
Jan 14 2014 04:33 PM |
|
2014-01-14 04:33:44 |
|
01/14/2014 at 01:33:44 |
|
01/14/2014 01:33:44-05:00 |
|
Strict DateTime format syntax
Use the following symbols to define format_string parameters for the StrictScanDateTime function. The count of pattern letters and presentation determines the format, as detailed below.
Symbol | Meaning | Presentation | Examples |
---|---|---|---|
| Era | text | AD; Anno Domini; A |
| Year | year | 2004; 04 |
| Year of era | year | 2004; 04 |
| Day of year | number | 189 |
| Month of year | number/text | 7; 07; Jul; July; J |
| Day of month | number | 10 |
| Quarter of year | number/text | 3; 03; Q3; 3rd quarter |
| Week-based year | year | 1996; 96 |
| Week of week-based year | number | 27 |
| Week of month | number | 4 |
| Day of week | text | Tue; Tuesday; T |
| Localized day of week | number/text | 2; 02; Tue; Tuesday |
| Week of month | number | 3 |
| AM/PM of day | text | PM |
| Clock hour of AM/PM (1-12) | number | 12 |
| Hour of AM/PM (0-11) | number | 0 |
| Clock hour of AM/PM (1-24) | number | 0 |
| Hour of day (0-23) | number | 0 |
| Minute of hour | number | 30 |
| Second of minute | number | 55 |
| Fraction of second | fraction | 978 |
| Milli of day | number | 1234 |
| Nano of second | number | 987654321 |
| Nano of day | number | 1234000000 |
| Time zone ID | zone-id | America/Los_Angeles; Z; -08:30 |
| Time zone name | zone-name | Pacific Standard Time; PST |
| Localized zone offset | offset-O | GMT+8; GMT+08:00; UTC-08:00; |
| Zone offset 'Z' for zero | offset-X | Z; -08; -0830; -08:30; -083015; -08:30:15; |
| Zone offset | offset-x | +0000; -08; -0830; -08:30; -083015; -08:30:15; |
| Zone offset | offset-Z | +0000; -0800; -08:00; |
| Escape for text | delimiter | |
| Single quote | literal | ' |
Text
The text style is determined based on the number of pattern letters used. Less than 4 pattern letters denotes the abbreviated form (Monday as Mon
). Exactly 4 pattern letters denotes the full form (Monday as Monday
). Exactly 5 pattern letters denotes the narrow form, typically a single letter (Monday as M
).
Number
If the count of letters is one, then the value is output using the minimum number of digits and without padding. Otherwise, the count of digits is used as the width of the output field, with the value zero-padded as necessary. The following pattern letters have constraints on the count of letters. Only one letter of F
(week of month) can be specified. Up to two letters of d
, H
, h
, K
, k
, m
, and s
can be specified. Up to three letters of D
can be specified.
Number/Text
If the count of pattern letters is 3 or greater, use the Text rules above. Otherwise use the Number rules above.
Fraction
Outputs the nano-of-second field as a fraction-of-second. The nano-of-second value has nine digits, thus the count of pattern letters is from 1 to 9. If it is less than 9, then the nano-of-second value is truncated, with only the most significant digits being output.
Year
The count of letters determines the minimum field width below which padding is used. If the count of letters is two, then a reduced two digit form is used. For printing, this outputs the rightmost two digits. For parsing, this will parse using the base value of 2000, resulting in a year within the range 2000 to 2099 inclusive.
ZoneId
This outputs the time-zone ID, such as "Europe/Paris." If the count of letters is two, then the time-zone ID is output. Any other count of letters produces an error.
Zone names
This outputs the display name of the time-zone ID. If the count of letters is one, two or three, then the abbrevaited name is output. If the count of letters is four, then the full name is output. Five or more letters produces an error.
Offset X and x
This formats the offset based on the number of pattern letters. One letter outputs just the hour, such as +01
, unless the minute is non-zero in which case the minute is also output, such as +0130
. Two letters outputs the hour and minute, without a colon, such as +0130
. Three letters outputs the hour and minute, with a colon, such as +01:30
. Four letters outputs the hour and minute and optional second, without a colon, such as +013015
. Five letters outputs the hour and minute and optional second, with a colon, such as +01:30:15
. Six or more letters produces an error. Pattern letter X
(upper case) will output Z
when the offset to be output would be zero, whereas pattern letter x
(lower case) will output +00
, +0000
, or +00:00
.
Offset O
This formats the localized offset based on the number of pattern letters. One letter outputs the short form of the localized offset, which is localized offset text, such as GMT
, with hour without leading zero, optional 2-digit minute and second if non-zero, and colon, for example GMT+8
. Four letters outputs the full form, which is localized offset text, such as GMT
, with 2-digit hour and minute field, optional second field if non-zero, and colon, for example GMT+08:00
. Any other count of letters produces an error.
Offset Z
This formats the offset based on the number of pattern letters. One, two or three letters outputs the hour and minute, without a colon, such as +0130
. The output will be +0000
when the offset is zero. Four letters outputs the full form of localized offset, equivalent to four letters of Offset-O. The output will be the corresponding localized offset text if the offset is zero. Five letters outputs the hour, minute, with optional second if non-zero, with colon. It outputs Z if the offset is zero. Six or more letters produces an error.
Time
Returns the Time portion of the DateTime.
Syntax
Time( date_time )
The required argument date_time must be of type DateTime.
Example
Hour(Time(CurrentDateTime(DATETIME))) >= 12
tests to see if the time portion of a DateTime field is in the PM.
UnixEpochMillisToDateTime
Converts a Unix epoch time value expressed in milliseconds into a DateTime value.
Syntax
UnixEpochMillisToDateTime( millisecondsSinceEpoch )
UnixEpochMillisToDateTime( millisecondsSinceEpoch, timezone )
Part | Description |
---|---|
millisecondsSinceEpoch | Required. A Unix time value. |
timezone | Optional. A text value containing the name or abbreviation of the original timezone. |
Remarks
This function optionally attempts to recognize timezone abbreviations (such as "MST" or "MDT" instead of "America/Denver"). However, these are ambiguous and should be avoided. View a list of supported timezone names.
Example
UnixEpochMillisToDateTime(epochMillis,"Etc/GMT")
returns the equivalent DateTime value for the Greenwich Mean Time time zone.
WindowsEpochTicksToDateTime
Converts a Windows epoch time value expressed in 100-nanosecond ticks into a DateTime value.
Syntax
WindowsEpochTicksToDateTime( ticksSinceEpoch )
WindowsEpochTicksToDateTime( ticksSinceEpoch, timezone )
Part | Description |
---|---|
ticksSinceEpoch | Required. A Windows time value. |
timezone | Optional. A text value containing the name or abbreviation of the original timezone. |
Remarks
This function optionally attempts to recognize timezone abbreviations (such as "MST" or "MDT" instead of "America/Denver"). However, these are ambiguous and should be avoided. View a list of supported timezone names.
Example
WindowsEpochTicksToDateTime(ticksSinceEpoch,"GMT")
returns the equivalent DateTime value for the Greenwich Mean Time time zone.