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

Note: Floating-point values supplied as integer arguments are rounded into integers.

<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

Tip: To create a date or a time string, either use @DateTimeCast, or construct the date or time string without using date/time functions (for example, by typing in "20170101").

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

Note: Floating-point values supplied as integer arguments are rounded into integers. Input values must be either all non-negative, or all non-positive. Mixed-signed input will be rejected and the result will be set to null.

<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

Tip: It is often easier to construct interval strings without using @DateTimeIntervalCreate(). Examples:

P1Y (one year from some datetime)

P@Value(days)D (Use value of "days" attribute as value of interval.)

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

Note: This function adheres to the following invariants, as long as input arguments are valid:

EndTime – StartTime = Interval iff

StartTime + Interval = EndTime

Although this invariant looks simplistic, it dictates unique answers to ambiguous computations such as 20170228 – 20160229 or 20160229 + P1Y.

Note: The interval is in ISO 8601 duration format. Larger units are added before smaller units, as ordering matters. For example, March 30 + P1M + P1D = May 1, but March 30 + P1D + P1M = April 30. So, the correct way to interpret + P1M1D is, 1 month is added first, then 1 day is added.

<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.

Note: This function adheres to the following invariants, as long as input datetimes are valid:

EndTime – StartTime = Interval iff

StartTime + Interval = EndTime

Although this invariant looks simplistic, it dictates unique answers to ambiguous computations such as 20170228 – 20160229 or 20160229 + P1Y.

Note: To reuse fractional years, months, days, hours, or minutes in a DateTimeCalculator transformer or @DateTimeAdd() function, round or truncate the fractional values into integers first.

Tip: Fractional years, months, days, hours, minutes, or seconds as the output interval type may be useful for comparison and reporting.

Tip: It is difficult to understand and work with negative intervals. Whenever possible, avoid negative intervals by ensuring that start datetimes occur before end datetimes.

Type Compatibility Matrix for <endDatetime> and <startDatetime>

	Date	Time	Time with Offset	Datetime	Datetime with Offset
Date	Yes	No	No	Yes	No
Time	No	Yes	No	No	No
Time with Offset	No	No	Yes	No	No
Datetime	Yes	No	No	Yes	No
Datetime with Offset	No	No	No	No	Yes

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

Note: A negated interval may not contain the same number of days as the original interval. For example, P1M from today might represent 31 days, and –P1M might represent 30 days. Whenever possible, use positive intervals, which are easier to work with.

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.

Note: If <unit> is days, only an interval of 1 is supported.

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.

Note: if <string> or <format> contains a comma (,) inside, it is necessary to wrap the argument containing the comma within quotes (""). Without the quotes, the comma acts as an argument separator.

Tip: @DateTimeParse() can be used to validate temporal strings, including those in FME format. If it outputs null, and the format string is verified to be correct, then the input temporal string is invalid. Additionally, if by setting <mode> to repair, a null output changes to a temporal value in Standard FME Date/Time Format, then the input string contained overflow parts (invalid) but was repaired.

Example: @DateTimeParse(20170229, %Y%m%d) = null. Input is invalid.

Example: @DateTimeParse(20170229, %Y%m%d,repair) = 20170301. Input is repaired.

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

Note: If <format> contains a comma (,) inside, it is necessary to wrap the argument containing the comma within quotes (""). Without the quotes, the comma acts as an argument separator.

Examples

See Format String Flags and Examples.