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

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/Time Format.

<targetType>

Can be one of date, time, or datetime, case-insensitive. Other values are rejected, with the result set to null.

Examples

@DateTimeCast(20170101,datetime) = 20170101000000

@DateTimeCast(20170101000000,date) = 20170101

@DateTimeCast(000000,date) = null

Note: Every temporal type can be cast to “time”. Only dates and datetimes can be cast to “date” and “datetime”. When a time component is missing, midnight (000000) is assumed.

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 1000 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>. If either argument is invalid, or if the <interval> contains parts that are not found in the <datetime>, null is returned.

Syntax

@DateTimeAdd(<datetime>,<interval>)

Arguments

<datetime>

A date, time, or datetime string in Standard FME Date/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(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. 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.

<startDatetime>

A date, time, or datetime string in Standard FME Date/Time Format.

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

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.

Parses strings containing datetime information from an arbitrary source, and outputs date, time, and datetime strings in Standard FME Date/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 temporal information must satisfy the following requirements, or else it is rejected and the result is set to null:

• Year, month, day are populated together, through a combination of %Y, %m, %d, %j, and %s format strings.
• 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. 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: %z (unless input is date only), %s, %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.

For supported format strings, see Format String Flags and Examples.

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

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

<format>

Format string containing supported format flags and regular characters. 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, reject:

%H %I %p %M %S %s %z %T

• If input is a time, reject:

%s %Y %y %m %b %B %d %j %a %A %z

• If input is a time with offset, reject:

%s %Y %y %m %b %B %d %j%a %A

• If input is a datetime, reject:

%z

• If input is a datetime with offset, reject:

None.