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:

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 )

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 )

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:

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 )

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 )

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.

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.

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 )

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 )

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.