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.

The count of pattern letters determine the format.

• Text: If the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available.

• Number: The minimum number of digits. Shorter numbers are zero-padded to this amount. When parsing, any number of digits are accepted.

• Year: Numeric presentation for year and weekyear fields are handled specially. For example, if the count of y is 2, the year will be displayed as the zero-based year of the century, which is two digits.

• Month: 3 or over, use text, otherwise use number.

• Millis: The exact number of fractional digits. If more millisecond digits are available then specified the number will be truncated, if there are fewer than specified then the number will be zero-padded to the right. When parsing, only the exact number of digits are accepted.

• Zone: Z outputs offset without a colon, ZZ outputs the offset with a colon, ZZZ or more outputs the zone ID.

• Zone names: Time zone names (z) cannot be parsed.

Any characters in the pattern that are not in the ranges of [a..z] and [A..Z] will be treated as quoted text. For instance, characters like :, ., , # and ? will appear in the resulting time text, even they are not embraced within single quotes.

DateTimeFormat is thread-safe and immutable, and the formatters it returns are as well.

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.