Date/Time Functions

Use the following functions to parse, format, and manipulate date, time, and datetime values anywhere in FME where expressions are supported. Unless stated otherwise, temporal arguments (date, time, datetime) must be in Standard FME Date/Time Format. For arithmetic support, see also DateTimeCalculator in the FME Workbench Transformers help.

Note: String arguments in date/time functions are automatically trimmed of leading and trailing whitespaces.

When a temporal arguments is processed, if the year, month, day, hours, minutes, and/or seconds are outside the supported range, the argument is rejected and the result is set to null. @DateTimeParse is the only date/time function that has an optional argument to repair parts that overflow supported ranges (except for years). For example, @DateTimeParse(20161332,%Y%m%d,repair) will repair the input date to 20170201.

Tip: Use @DateTimeParse to repair leap seconds and other temporal values containing parts that overflow supported ranges, such as datetime values generated by sensors and other mechanical devices.

Summary of Date/Time Functions

Function Description Examples
@DateTimeAdd() Datetime + Interval = Datetime

@DateTimeAdd(20170102, P1M) = 20170202

@DateTimeAdd(20170102, -P1M) = 20161202
@DateTimeCast()

Casts source type to destination type, if conversion is safe.

@DateTimeCast(20170102123000-08, time) = 123000-08:00

@DateTimeCast(20170132, date) = null

@DateTimeCast(20170001, date) = null

@DateTimeCreate() Creates datetime strings, unzoned or with UTC offset. @DateTimeCreate(2017,1,2,12,30,0,-08) = 20170102123000-08:00
@DateTimeDiff() Datetime – Datetime = Interval

@DateTimeDiff(20170201,20170101) = P1M

@DateTimeDiff(20170202, 20170201, seconds) = 86400
@DateTimeFormat() Formats FME datetime strings for consumption by writers and transformers. @DateTimeFormat(20170102,%B %d %Y) = January 02 2017
@DateTimeIntervalCreate() Creates interval strings in ISO 8601 duration format. @DateTimeIntervalCreate(1,0,0,0,0,0) = P1Y
@DateTimeIntervalNegate() Negates intervals. @DateTimeIntervalNegate(P1Y) = -P1Y
@DateTimeNow() Creates timestamps in local, local unzoned, or UTC time.

@DateTimeNow() = 20170203170000.1234567

@DateTimeNow(localUnzoned) = 20170203170000.1234567

@DateTimeNow(local) = 20170203170000.1234567-08:00

@DateTimeNow(utc) = 20170204010000.1234567+00:00

@DateTimeParse()

Parses arbitrary strings containing date time information into datetime strings in FME format.

Can be used for validation and repair. Can parse leap seconds by carrying the extra second forward so that all parts are brought in range.

@DateTimeParse(January 2 2017, %B %d %Y) = 20170102

@DateTimeParse(20170229,%Y%m%d,repair) = 20170301

Detailed Date/Time Function Descriptions

Date/Time Construction Functions

Closed@DateTimeCast

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.

Closed@DateTimeCreate

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

Closed@DateTimeIntervalCreate

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:

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

Closed@DateTimeNow

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:

Examples

@DateTimeNow() = 20170202162035.1574322

@DateTimeNow(localUnzoned) = 20170202162035.1574322

@DateTimeNow(local) = 20170202162035.1574322-08:00

@DateTimeNow(utc)

= 20170203002035.1574322+00:00

Date/Time Arithmetic Functions

Closed@DateTimeAdd

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.

Closed@DateTimeDiff

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

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.

Closed@DateTimeIntervalNegate

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.

Date/Time Parse/Format Functions

Closed@DateTimeParse

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:

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

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.

Closed@DateTimeFormat

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:

Format String Flags and Examples

Type Flag Meaning @DateTimeParse @DateTimeFormat
Year %Y Four-digit year, with range 1000 to 9999. @DateTimeParse(2017-01-02, %Y-%m-%d) = 20170102 @DateTimeFormat(20170102, %Y-%m-%d) = 2017-01-02
%y Two-digit year. Not supported. @DateTimeFormat(20170102, %y-%m-%d) = 17-01-02
Month %m Two-digit month, with range 01 to 12.

One digit month accepted.

@DateTimeParse(2017-1-2, %Y-%m-%d) = 20170102

@DateTimeFormat(20170102, %Y-%m-%d) = 2017-01-02
%b Abbreviated, 3-letter English month name. @DateTimeParse(Jan 2 2017,%b %d %Y) = 20170102 @DateTimeFormat(20170102, %b %d %Y) = Jan 02 2017
%B   @DateTimeParse(January 2 2017,%B %d %Y) = 20170102 @DateTimeFormat(20170102, %B %d %Y) = January 02 2017
Day %d Two-digit day of month, with range 01 to the last day of the month.

One digit day accepted.

@DateTimeParse(2017-1-2, %Y-%m-%d) = 20170102

 @DateTimeFormat(20170102, %Y-%m-%d) = 2017-01-02
%j Three-digit day of year, with range from 001 to 365 (non-leap year) or 366 (leap year).

One and two digit days of year accepted.

@DateTimeParse(20171,%Y%j) = 20170101

@DateTimeFormat(20170101,%Y-%j) = 2017-001

@DateTimeFormat(20161231,%Y-%j) = 2016-366
%a Abbreviated, 3-letter weekday names. Not supported. Consider parsing using *. @DateTimeFormat(20170102, %a %b %d %Y) = Mon Jan 02 2017
%A Full weekday names. Not supported. Consider parsing using *. @DateTimeFormat(20170102, %A %B %d %Y) = Monday January 02 2017
Hours %H Two-digit hours, with range from 00 to 23.

One digit hours value accepted.

@DateTimeParse(8:30, %H:%M) = 083000

 @DateTimeFormat(083000, %H:%M:%S) = 08:30:00
%I 12-hour clock, with range 01 to 12.

One digit hours value accepted.

@DateTimeParse(8:30PM, %I:%M%p) = 203000

 @DateTimeFormat(003000, %I:%M %p) = 12:30 AM
%p AM/PM

AM/PM accepted, case insensitive.

@DateTimeParse(12:30am, %I:%M%p) = 003000

 @DateTimeFormat(083000, %I:%M %p) = 08:30 AM
Minutes %M Two-digit minutes, with range 00 to 59.

One digit minutes value accepted.

@DateTimeParse(8:30, %H:%M) = 083000

 @DateTimeFormat(083000, %H:%M:%S) = 08:30:00
Seconds %S Two-digit seconds, with range 00 to 59. Fractional seconds are supported up to nanosecond resolution.

One digit seconds value accepted.

@DateTimeParse(8:30:01.234, %H:%M:%S) = 083001.234

 @DateTimeFormat(083059.123, %H:%M:%S) = 08:30:59.123
%s Seconds since the Unix Epoch time (1970-01-01 00:00:00+00:00). Fractional seconds are supported up to nanosecond resolution. If input is unzoned, it is assumed to be UTC time.

The seconds part must have at least a single digit. For example, "0.1" is considered a valid value for %s, but ".1" is not.

If %z is not specified, output is in UTC time (+00:00).

If %z is specified, the output datetime will have the same UTC offset as the input string.

@DateTimeParse(1.234,%s) = 19700101000001.234+00:00

@DateTimeParse(1.234-08:00,%s%z) = 19700101000001.234-08:00

 @DateTimeFormat( 19700101000001.234,%s) = 1.234 @DateTimeFormat( 19700101000001+00:00,%s) = 1
Timezone %z UTC offset in the form +/-hh:mm.

UTC offset in the form +/-hh, +/-hhmm, or +/-hh:mm, or Z for Zulu time which is +00:00.

@DateTimeParse(8:30-08,%H:%M%z) = 083000-08:00

@DateTimeParse(03:04:05Z,%H:%M:%S%z) = 030405+00:00

 @DateTimeFormat(000000-08:00,%T%z) = 00:00:00-08:00
Shortcuts %T Equal to %H:%M:%S. @DateTimeParse(03:04:05,%T) = 030405 @DateTimeFormat(000000,%T) = 00:00:00
FME

Auto parses input that is in the Standard FME Date/Time Format, and repairs overflow parts if the repair argument is set.

It can be combined with the flag ISO, separated by the | character.

To disambiguate this shortcut from a regular text string of the same content, when FME and ISO are specified, there must be no other format flags present.

@DateTimeParse(030460-0800, FME, repair) = 030500-08:00

@DateTimeParse(030460-0800, FME|ISO, repair) = 030500-08:00

 Not supported.
ISO

Auto parses/formats input into ISO datetime format.

To disambiguate this shortcut from a regular text string of the same content, when ISO is specified, there must be no other format flags present.

@DateTimeParse(2017-01-02, FME | ISO) = 20170102

@DateTimeParse(03:04:60,ISO,repair) = 030500

@DateTimeFormat(20170102, ISO) = 2017-01-02

@DateTimeFormat(20170102030405-08,ISO) = 2017-01-02T03:04:05-08:00
Wildcard Character * A single word, delimited by a whitespace or the start/end of string. @DateTimeParse(On Monday January 23 2017,* * %B %d %Y) = 20170123 Not supported.
? A single character.

@DateTimeParse(January 23rd 2017,%B %d?? %Y) = 20170123

@DateTimeParse(LC80460262015110LGN00, LC???????%Y%jLGN??) = 20150420

 Not supported.
$ End of input. Used to ensure that the input fully matches the format string.

@DateTimeParse(2017-01-02, %Y-%m-%d$) = 20170102

@DateTimeParse(2017-01-02 and then, %Y-%m-%d$) = null

 Not supported.
Special Character %% The % character. @DateTimeParse(2017%01%05,%Y%%%m%%%d) = 20170105 Not supported.
%* The * character. @DateTimeParse(*** 2017-01-05,%*%*%* %Y-%m-%d) = 20170105 Not supported.
%? The ? character. @DateTimeParse(On Jan 2 2017?,* %b %d %Y%?) = 20170102 Not supported.
%$ The $ character. @DateTimeParse(2017$01$02,%Y%$%m%$%d) = 20170102 Not supported.
Regular Character Any Any character that is not prepended by % is considered a regular character. Case sensitivity is respected.

@DateTimeParse(On Jan 23 2017, On %b %d %Y) = 20170123

@DateTimeParse(On Jan 23 2017, on %b %d %Y) = null

 @DateTimeFormat(20170102,On %B %d %Y) = On January 02 2017