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 or in ISO 8601 date and 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 argument 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.

An optional third argument specifies the type of cast operation. See @DateTimeCast for details.

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

@DateTimeCast(20170132, date) = null

@DateTimeCast(20170001, 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

@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

@DateTimeRound()

Rounds a datetime string to a given interval.

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

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

@TimeZoneGet

Retrieves the UTC offset of zoned datetime values.

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

@TimeZoneRemove

Removes the UTC offset of zoned datetime values.

@TimeZoneRemove(083000-08) = 083000

@TimeZoneSet

Sets a time zone (UTC offset) onto an unzoned datetime, or converts a zoned datetime into another time zone.

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

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

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

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

Detailed Date/Time Function Descriptions

Date/Time Construction Functions

Date/Time Arithmetic Functions

Date/Time Time Zone Functions

Date/Time Parse/Format Functions

Format String Flags and Examples

Type

Flag

Meaning

@DateTimeParse

@DateTimeFormat

Year

%Y

Four-digit year, with range 0001 to 9999.

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

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

%y####

Two-digit year, where #### is the greatest (four-digit) year to consider.

For example, if %y2059 is specified, two-digit years 00-59 are read as 2000-2059, whereas 60-99 are read as 1960-1999.

@DateTimeParse(17-01-02, %y2020-%m-%d) = 20170102

@DateTimeParse(17-01-02, %y2010-%m-%d) = 19170102

@DateTimeParse(17-01-02, %y2017-%m-%d) = 20170102

Not supported.

%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 if separated by delimiters.

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

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

%N

Same as %m except as noted.

@DateTimeParse(1/2/2017,%N/%e/%Y) = 20170102

Same as %m, except leading zeros are removed.

@DateTimeFormat(20170102,%N/%e/%Y) = 1/2/2017

%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

Week

%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

%u

Single digit day of week, with range 1 to 7, Monday being 1.

Not supported.

@DateTimeFormat(20170102, %u) = 1

%U

Two digit week of year, with range 00 to 53, starting with the first Sunday as the first day of week 01.

Not supported.

@DateTimeFormat(20160102, %U) = 00

@DateTimeFormat(20160103, %U) = 01

@DateTimeFormat(20171231, %U) = 53

%w

Single digit day of week, with range 0 to 6, Sunday being 0.

Not supported.

@DateTimeFormat(20170101, %w) = 0

%W

Two digit week of year, with range 00 to 53, starting with the first Monday as the first day of week 01.

Not supported.

@DateTimeFormat(20160103, %W) = 00

@DateTimeFormat(20160104, %W) = 01

@DateTimeFormat(20181231, %W) = 53

Day

%d

Two-digit day of month, with range 01 to the last day of the month.

One digit day accepted if separated by delimiters.

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

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

%e

Same as %d except as noted.

@DateTimeParse(1/2/2017,%N/%e/%Y) = 20170102

Same as %d, except leading zeros are removed.

@DateTimeFormat(20170102,%N/%e/%Y) = 1/2/2017

%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

Hours

%H

Two-digit hours, with range from 00 to 23.

One digit hours value accepted if separated by delimiters.

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

@DateTimeFormat(083000, %H:%M:%S) = 08:30:00

%k

Same as %H except as noted.

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

Same as %H, except leading zeros are removed.

@DateTimeFormat(083000, %k:%M%p) = 8:30AM

%I (i)

12-hour clock, with range 01 to 12.

One digit hours value accepted if separated by delimiters.

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

@DateTimeFormat(203000, %I:%M %p) = 08:30PM

%l (L)

Same as %I, except as noted.

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

Same as %I, except leading zeros are removed.

@DateTimeFormat(203000, %l:%M%p) = 8:30PM

%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 if separated by delimiters.

@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 if separated by delimiters.

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

Format string is rejected if both %z and %s are present.

Accepts epoch time. Outputs UTC datetime.

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.

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

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

Accepts zoned datetime. Outputs epoch time.

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

@DateTimeFormat( 19700101000001.234, %s) = null

@DateTimeFormat( 19700101000001.234+00:00, %s%z) = null

%Es

Same as %s, except this flag supports unzoned dates and datetimes.

Format string is rejected if both %z and %Es are present.

Accepts epoch time. Outputs UTC datetime, but with the UTC offset removed. So output is unzoned.

@DateTimeParse(1.234, %Es) = 19700101000001.234

@DateTimeParse(1.234, %Es%z) = null

Accepts unzoned date or datetime and treats it as UTC time. Outputs epoch time.

@DateTimeFormat( 19700101000001.234, %Es) = 1.234

@DateTimeFormat(19700102, %Es) = 86400

@DateTimeFormat( 19700101000001.234+00:00, %Es) = null

@DateTimeFormat(19700101000001.234, %Es%z) = null

Time Zone

%z

UTC offset in the form +/-hh:mm, except as noted.

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

@DateTimeFormat(000000-08:00,%T%z) = 00:00:00-08:00

%Ez

Same as %z, except as noted.

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

Same as %Ez, except +00:00 is output as Z for Zulu time.

@DateTimeFormat(030405+00:00,%T%Ez)=03:04:05Z

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.

Specifically, @DateTimeParse() looks for the following matches:

  • %Y%m%d$
  • %H%M%S$
  • %H%M%S%Ez$
  • %Y%m%d%H%M%S$
  • %Y%m%d%H%M%S%Ez$

It can be combined with the flag ISO, separated by the vertical bar (pipe) | 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

@DateTimeFormat(083000-08, FME) = 083000-08:00

@DateTimeFormat(20170229, FME) = null

ISO

Auto parses/formats input into ISO datetime format.

Specifically, @DateTimeParse() looks for the following matches:

  • %Y-%m-%d$
  • %H:%M:%S$
  • %H:%M:%S%Ez$
  • %Y-%m-%dT%H:%M:%S$
  • %Y-%m-%dT%H:%M:%S%Ez$

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, which matches a sequence of characters delimited either by whitespaces, the end of string, or the character immediately following the * character.

For example, "Monday," can be parsed by either "*" or "*,".

The following are special characters that might follow the * wildcard and what they mean:

Sequence Description

**

Reject input.

*?

Reject input.

*$

$ is treated as end of string.

*%

Keep parsing. All % flags except below are rejected.

*%%

Word that ends with %

*%*

Word that ends with *

*%?

Word that ends with ?

*%$

Word that ends with $

@DateTimeParse("On Monday, January 23 2017","* * %B %d %Y") = 20170123

@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 string. Used to ensure that the input fully matches the format string.

Input strings are auto-trimmed of trailing whitespaces.

@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

@DateTimeFormat(020304,%T %%) = 02:03:04 %

%*

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 a wildcard or special character as documented above 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

See Also

DateTimeCalculator

DateTimeConverter

DateTimeStamper

Standard FME Date/Time Format