Date/Time Functions

Casts a temporal value to the specified temporal type.

Returns

A date, time, or datetime string in Standard FME Date/Time Format if the input temporal value is successfully cast to the destination temporal type, or null otherwise.

Syntax

@DateTimeCast(<datetime>,<targetType>[,<castType>])

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format. When a time component is missing, midnight (000000) is assumed.

<targetType>

Can be one of date, time, or datetime, case-insensitive. Other values are rejected, with the result set to null. If <castType> is strict_offset or exact_match, <targetType> can also be time_offset or datetime_offset.

<castType>

Can be preserve_offset, strict_offset, or exact_match.

• preserve_offset: If <datetime> includes an offset, it is preserved in the output.
• strict_offset: If <datetime> does not have an offset (offsets do not have a default value) and <targetType> is time_offset or datetime_offset, null is returned. If <targetType> is time or datetime, any offset is dropped.
• exact_match: Acts as a validator. If <datetime> does not exactly match <targetType>, null is returned.

Examples

@DateTimeCast(20170101,datetime) = 20170101000000

@DateTimeCast(20170101000000,date) = 20170101

@DateTimeCast(000000,date) = null

@DateTimeCast(20180821, time, exact_match) = null

@DateTimeCast(120000+08:00, time_offset, strict_offset) = 120000+08:00

@DateTimeCast(120000, time_offset, strict_offset) = null

Creates a datetime string.

Returns

A datetime string in FME Format, containing a UTC offset if a non-empty offset is supplied. If any input argument is invalid, null is returned.

Syntax

@DateTimeCreate(<year>,<month>,<day>,<hours>,<minutes>,<seconds>,<offset>)

Arguments

<year>

Integer between 1 and 9999.

<month>

Integer between 1 and 12.

<day>

Integer between 1 and 31.

<hours>

Integer between 0 and 23.

<minutes>

Integer between 0 and 59.

<seconds>

Floating-point value between 0 and 59.999999999.

<offset>

String in the form +/-hh, +/-hhmm, or +/-hh:mm. Can also be the empty string, "", to indicate unzoned time.

Examples

@DateTimeCreate(2017,1,2,0,0,0,"") = 20170102000000

@DateTimeCreate(2017,0,1,0,0,0) = null

Creates an interval representing a period of time such as 1 month, 1 year, 1 day, and so on.

Returns

A string in ISO 8601 duration format, except fractional years, months, days, hours, and minutes are not supported. If any input argument is invalid, null is returned.

Syntax

@DateTimeIntervalCreate(<years>,<months>,<days>,<hours>,<minutes>,<seconds>[,<sign>])

Arguments

<years>

Integer.

<months>

Integer.

<days>

Integer.

<hours>

Integer.

<minutes>

Integer.

<seconds>

Integer.

<sign>

Optional string value. Can be one of the following:

• Unspecified: The interval is positive.
• +: The interval is positive.
• −: The interval is a negative interval. For example, P1M means one month from some date, and –P1M means negative one month from some date.
• Other values: Argument is rejected and the result is set to null.

Examples

@DateTimeIntervalCreate(1,2,0,0,0,0.123) = P1Y2MT0.123S

@DateTimeIntervalCreate(1,0,-1,0,0,0) = null

@DateTimeIntervalCreate(0,0,1,0,0,0,-) = -P1D

Outputs the current datetime, honoring the precision of the system clock.

Returns

A datetime string in Standard FME Date/Time Format with the specified offset type. If the offset type is invalid, null is returned.

Syntax

@DateTimeNow([<offsetType>])

Arguments

<offsetType>

Optional string that can be one of the following values, case-insensitive:

• Unspecified: Same as localUnzoned.
• localUnzoned: Output local time, but no UTC offset is appended.
• local: Output local time, with local UTC offset appended. For example, Pacific Standard Time has a UTC offset of -08:00.
• utc: Output UTC time, with offset +00:00.
• Other values: Argument is rejected and the result is set to null.

Examples

@DateTimeNow() = 20170202162035.1574322

@DateTimeNow(localUnzoned) = 20170202162035.1574322

@DateTimeNow(local) = 20170202162035.1574322-08:00

@DateTimeNow(utc)

= 20170203002035.1574322+00:00

Adds an interval, or period of time, to a datetime. To subtract from a datetime, negate the <interval>. For example, <interval> = P1M adds a month to <datetime>. <interval> = -P1M subtracts a month from <datetime>.

Returns

A datetime string in Standard FME Date/Time Format that has the same temporal type as the input <datetime>, where input dates are autocast into datetimes by adding midnight if <interval> contains a time component. If either argument is invalid, or if the <interval> contains parts that are not found in the <datetime> argument, null is returned.

Syntax

@DateTimeAdd(<datetime>,<interval>)

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format.

<interval>

A string in ISO 8601 duration format, except fractional years, months, days, hours, and minutes are not supported.

Examples

@DateTimeAdd(20170102,P1M) = 20170202

@DateTimeAdd(20160229,P1Y) = 20170228

@DateTimeAdd(20160229,-P1Y) = 20150228

@DateTimeAdd(000000,PT1H) = 010000

@DateTimeAdd(20170102,PT1H) = 20170102010000

@DateTimeAdd(000000,P1DT1H) = null

<endDatetime> – <startDatetime> = interval. The start and end values must have the same temporal type, or the result is null. The only exception is that dates are autocast as datetimes by adding midnight if the other argument is a datetime. This function supports several types of intervals.

Returns

An interval value whose type is defined by <intervalType>. If any input argument is invalid, or if the start and end values are not of the same temporal type (for example, date - datetime), null is returned.

Syntax

@DateTimeDiff(<endDatetime>,<startDatetime>[,<intervalType>])

Arguments

<endDatetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format. <endDatetime> must be type-compatible with <startDatetime>. See Type Compatibility Matrix for <endDatetime> and <startDatetime> below.

<startDatetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format. <startDatetime> must be type-compatible with <endDatetime>. See Type Compatibility Matrix for <endDatetime> and <startDatetime> below.

<intervalType>

Can be one of the following string values, case-insensitive:

• Unspecified: Outputs the interval in ISO 8601 duration format.

• Interval: Outputs the interval in ISO 8601 duration format.
• Years: Outputs the interval as fractional years, where a year equal 12 months.
• Months: Outputs the interval as fractional months. Between a pair of start and end temporal values there are X full months. The remainder that is not a full month consists of a number of days, hours, minutes, and seconds. The remainder is first converted into fractional days. Then, the fractional days are divided by the number of days in a crossover month. The crossover month precedes the remainder, and guarantees that the remainder is never greater than a month, when expressed as a fractional month. Finally, the remainder as a fractional month is added to the X full months, to give us the resulting fractional months.
• Weeks: Outputs the interval as fractional weeks, assuming 7 days in a week.
• Days: Outputs the interval as fractional days, assuming 24 hours in a day.
• Hours: Outputs the interval as fractional hours, assuming 60 minutes in an hour.
• Minutes: Outputs the interval as fractional minutes, assuming 60 seconds in a minute.
• Seconds: Outputs the interval as fractional seconds.

• Other values: Argument is rejected and the result is set to null.

Type Compatibility Matrix for <endDatetime> and <startDatetime>

Incompatible end/start types result in feature rejection.

Examples

@DateTimeDiff(20170407, 20170101) = P3M6D

@DateTimeDiff(20170407, 20170101, months) = 3.2000000000000002

@DateTimeDiff(20170407, 20170101, days) = 96

@DateTimeDiff(20170102010000, 20170102) = PT1H

@DateTimeDiff(20170102, 20170131020304-08:00) = null

Negates interval strings. This function can be used to turn @DateTimeAdd() into a subtraction function.

Returns

If the input <interval> is invalid, null is returned. Otherwise, the input <interval> with its sign flipped. This means either prepending a "-" character, or removing that character.

Syntax

@DateTimeIntervalNegate(<interval>)

Arguments

<interval>

A string in ISO 8601 duration format, except fractional years, months, days, hours, and minutes are not supported.

Examples

@DateTimeIntervalNegate(-P1M1D) = P1M1D

@DateTimeIntervalNegate(P1M1Y) = null

Returns

A datetime string in Standard FME Date/Time Format. If the input datetime is invalid, null is returned.

Syntax

@DateTimeRound(<datetime>,<round_type>,<unit>,<interval>)

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format.

<round_type>

The direction to round:

• Up: Rounds to the next interval of the specified <unit>.
• Down: Rounds to the previous interval of the specified <unit>.
• Nearest: Rounds to the nearest interval of the specified <unit>.

<unit>

The rounding unit of time. Valid values are:

• days
• hours
• minutes
• seconds

<interval>

The rounding interval for the specified <unit>. This value must be an integer that divides evenly into the maximum value of the selected unit of time. For example, an interval value of 5 is valid for minutes because it divides evenly into 60.

Examples

@DateTimeRound(20170203170000.1234567,down,seconds,1) = 20170203170000

@DateTimeRound(20170203170000.1234567,up,seconds,5) = 20170203170005

@DateTimeRound(20170203170000.1234567,nearest,hours,5) = 20170203150000

Retrieves the UTC offset of a zoned datetime.

Returns

The UTC offset of the input datetime if it is zoned, and null otherwise. If the input is not an FME datetime value, null is returned. The UTC offset returned is converted into the return type specified.

Syntax

@TimeZoneGet(<datetime>[,<returnType>])

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format.

<returnType>

Optional. Applies only if input datetime is zoned. Must be one of the following values, case-insensitive, or the input is rejected:

• Unspecified: Same as utcOffset.
• utcOffset: Returns the UTC offset in the form (+|-)hh:mm.
• minutes: Returns the UTC offset as minutes.
• seconds: Returns the UTC offset as seconds.

Examples

@TimeZoneGet(083000-08) = -08:00

@TimeZoneGet(083000+08) = +08:00

@TimeZoneGet(083000-01:30, minutes) = -90

@TimeZoneGet(083000+01:30, minutes) = 90

@TimeZoneGet(083000-01:30, seconds) = -5400

@TimeZoneGet(083000) = null

Removes the UTC offset of a datetime.

Returns

If the input datetime is zoned, returns the input datetime with the UTC offset removed.

If the input datetime is unzoned, returns the input datetime unchanged.

If the input is not an FME or ISO datetime value, null is returned.

Syntax

@TimeZoneRemove(<datetime>)

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format.

Examples

@TimeZoneRemove(083000-08) = 083000

@TimeZoneRemove(083000) = 083000

@TimeZoneRemove(08:30) = null

Converts/sets the input datetime's UTC offset.

Returns

If the input datetime is zoned, returns the input datetime with its UTC offset converted into, or reset as, the specified UTC offset.

If the input datetime is unzoned, returns the input datetime with the specified UTC offset appended.

If one of the input arguments is invalid, null is returned.

Syntax

@TimeZoneSet(<datetime>, <timeZone>[, <mode>])

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format.

<timeZone>

Can be one of the following values, case-insensitive:

• local: The UTC offset of the local time zone that was in effect at the time of the datetime value. In convert mode, this converts a datetime-with-offset value to the equivalent local time, honoring daylight savings time rules. In reset mode, any existing offset is ignored, and the datetime is associated with a UTC offset applicable for local time. (reset mode fails if there is no associated local time due to a skipped hour when daylight savings time takes effect, or during an ambiguous repeated hour when standard time takes effect.)
• utc: The UTC offset +00:00.
• A UTC offset in the form (+|-)hh:mm.

<mode>

Must be one of the following values, case-insensitive, or the input is rejected:

• Unspecified: Same as auto.
• auto: Sets time zone if input is unzoned, and converts time zone if input is zoned.
• reset: Removes any UTC offset in the input datetime, and then sets its UTC offset to the target <timeZone>.
• convert: Unzoned input is rejected. Zoned input is converted from the source UTC offset to the target <timeZone>.

Examples

@TimeZoneSet(083000, -08:00) = 083000-08:00

@TimeZoneSet(083000, -08:00, convert) = null

@TimeZoneSet(083000-01:00, -08:00) = 013000-08:00

@TimeZoneSet(083000-01:00, local) = null

@TimeZoneSet(083000-01:00, utc) = 093000+00:00

@TimeZoneSet(083000-01:00, -08:00, reset) = 083000-08:00

Parses strings containing datetime information from an arbitrary source, and outputs date, time, and datetime strings in Standard FME Date/Time Format or ISO 8601 date and time format. The format string is case sensitive, except as noted. The parse function iterates through the input string and matches it against the format string.

Returns

Date, time or datetime string in Standard FME Date/Time Format, depending on format flags. If the input string is fully matched before the format string is fully applied, the result is set to null. However, the format string is permitted to match the start of an input string. For example, @DateTimeParse(2017-01-02 03:04:05,%Y-%m-%d) = 20170102. If the format string caused no temporal information to be parsed, or if one of the parse flags failed to fetch the expected temporal information, the result is set to null.

Syntax

@DateTimeParse(<string>,<format>[,<mode>])

Arguments

<string>

Arbitrary input string containing temporal information. The input string is trimmed of leading and trailing whitespaces. The temporal information must satisfy the following requirements, or else it is rejected and the result is set to null:

• Year, month, day are provided together, in any order, through a combination of format flags such as %Y-%m-%d.
• If hours are populated through %H or %I format strings, but not minutes or seconds, the latter are assigned default values of 00. This allows input in the form 12AM or 3PM to be parsed.
• Similarly, if hours and minutes are populated, but not seconds, the seconds part is assigned a default value of 00.

<format>

Format string containing supported format flags, regular characters, and special characters. The format string is trimmed of leading and trailing whitespaces. Empty format strings and unsupported format flags cause the result to be set to null.

The precedence of parse flags, from highest precedence to lowest, is: %s or %Es, %z, %j, all other flags. When the other flags overlap—for example, if %B and %m are both specified—then the one that is specified last, prevails.

<mode>

Optional string value. Can be one of the following, case-insensitive:

• Unspecified: Input temporal parts must respect the ranges documented.
• repair (case insensitive): Underflow will be rejected (for example, month 0) but overflow parts (other than years) will be repaired so that all parts are brought in range (for example, month 13 will cause year to increment by 1, and month will set to 1). %I is not covered by this mode, as it is ambiguous how 13:00AM should be repaired—as 13:00 (treat AM as extraneous), or 01:00 (the hour after 12AM)?
• Other values: Argument is rejected and the result is set to null.

Examples

See Format String Flags and Examples.

Formats FME or ISO date, time, and datetime strings into a custom format, for consumption by writers and transformers. The format string is case sensitive.

Returns

A formatted string, if input arguments are valid. If either argument is invalid, (for example, the format string contains unsupported flags given the temporal type of the input datetime), this function returns null.

Syntax

@DateTimeFormat(<datetime>,<format>)

Arguments

<datetime>

Date, time, or datetime string in Standard FME Date/Time Format or ISO 8601 date and time format. Input dates will be autocast into datetimes by adding midnight if format string outputs time components.

<format>

Format string containing supported format flags and regular characters. The format string is trimmed of leading and trailing whitespaces. Empty format strings and unsupported format flags cause the result to be set to null. Some supported format flags are rejected for certain types of input temporal types, causing the result to be set to null. Specifically:

• If input is a date or datetime, reject:

%s %z %Ez

• If input is a time or time with offset, reject:

%s %Es %Y %y %m %N %b %B %u %U %w %W %d %e %j %a %A

• If input is a time, further to the above flags, reject:

%z %Ez

• If input is a datetime with offset, reject:

%Es

Examples

See Format String Flags and Examples.