• Date & Time Patterns
  • Elements of a pattern
    • Time elements
    • Static elements
  • Defining a pattern
    • In the mapping window
    • In an expression
  • Examples
    • In the mapping window
    • In an expression

Date & Time Patterns

This page explains how to define a pattern to output a certain date, time or dateTime in a certain way. You specify the pattern according to the rules and elements specified below.

You can specify custom date & time patterns in the date & time functions, or in the custom date option as described on this page:

Elements of a pattern

A pattern consists of time elements and, optionally, static elements. Time elements represent a time unit, such as a year, a month, a day, but also an AM/PM notation, a timezone etc.

For instance:

• yyyy-MM-dd = 2015-07-15 for July 15th in 2015. Both - are static elements, all the rest are time elements.
• dd/MM/yyyy = 15/07/2015 for July 15th in 2015. Both / are static elements, all the rest are time elements.
• dd 'days' = 30 days for a period of 30 days. The word days is static, dd is the time element representing days.

Using both time and static elements allows you to make your own custom date & time patterns.

Time elements

You can use the following time elements in the pattern by just putting their symbol directly into the pattern.

We'll use the following datetime as the example: 2015, July 15th, 03:45:30. Locale: English (en). Time zone: Pacific Standard Time

Static elements

You can use both symbols and letters as static elements. You can use the following symbols by putting them directly in the pattern:

/ * - + & ! ( ) { } [ ] , . ; : < > _ - # @

If you want to use a ' (apostrophe), you need to put 2 of them, like so:

''

If you want to use letters, you need to put them in between apostrophes, like so:

'a'  'days'  'a whole sentence'

Putting that all together, if you want your pattern to say 7 o'clock (where 7 is the hour), here's the pattern you need to specify:

h 'o''clock'

Defining a pattern

In the mapping window

When you have a date in a field, or as the output of an expression, you can use the custom date option in the Format tab of the mapping window to specify a pattern:

Just start typing into the box next to Pattern or select the ... to select a pre-made pattern, that you can then adjust.

In an expression

Defining a pattern in an expression is necessary for quite a few functions, such as formatDate, formatTime, toDateF, toTimeF etc. For instance:

formatDate(currentDate(),'yyyy-MM-dd')formatTime(currentTime(),'hh:mm')

As always with expressions, you need to put strings (such as a date & time pattern) in between ' ' (apostrophes).

To remedy this, you can escape your apostrophes by putting a \ (backslash) in front of them. So, instead of...

dd 'days'h 'o''clock' 

...you type this:

dd \'days\'h \'o\'\'clock\' 

It may look a bit strange, but it will definitely get the job done. Here are examples of the above patterns. Notice the apostrophe before and after the entire pattern:

formatPeriod(10000000,'dd \'days\'')

formatDate(currentDate(),'h \'o\'\'clock\'') 

Examples

Let's end with a few examples. For all of the following, the given date and time are 2001-07-04 12:08:56 local time in the U.S. Pacific Time time zone.

In the mapping window

These patterns don't require apostrophes like expressions would.

In an expression

The following patterns include the apostrophes used in an expression to delineate the beginning and ending of a string.