Structuring dates and times

Epoch
Date formats
Time formats
Date and time picture formats
Locales

Epoch

Date values and time values have an associated origin or epoch, which is a moment in time from which time begins. Any date value and any time value prior to its epoch is invalid.

The unit of value for all date functions is the number of days since the epoch. The unit of value for all time functions is the number of milliseconds since the epoch.

Designer defines day one for the epoch for all date functions as Jan 1, 1900, and millisecond one for the epoch for all time functions is midnight, 00:00:00, Greenwich Mean Time (GMT). This definition means that negative time values can be returned to users in time zones east of GMT.

Date formats

A date format is a shorthand specification of how a date appears. It consists of various punctuation marks and symbols that represent the formatting that the date must use. The following table lists examples of date formats.

Date format	Example
MM/DD/YY	11/11/78
DD/MM/YY	25/07/85
MMMM DD, YYYY	March 10, 1964

The format of dates is governed by an ISO standard. Each country or region specifies its own date formats. The four general categories of date formats are short, medium, long, and full. The following table contains examples of different date formats from different locales for each of the categories.

Locale identifier and description	Date format (Category)	Example
en_GB English (United Kingdom)	DD/MM/YY (Short)	08/12/92 08/04/05
fr_CA French (Canada)	YY-MM-DD (Medium)	92-08-18
de_DE German (Germany)	D. MMMM YYYY (Long)	17. Juni 1989
fr_FR French (France)	EEEE, ' le ' D MMMM YYYY (Full)	Lundi, le 29 Octobre, 1990

Time formats

A time format is a shorthand specification to format a time. It consists of punctuations, literals, and pattern symbols. The following table lists examples of time formats.

Time format	Example
h:MM A	7:15 PM
HH:MM:SS	21:35:26
HH:MM:SS 'o''clock' A Z	14:20:10 o’clock PM EDT

Time formats are governed by an ISO standard. Each nation specifies the form of its default, short, medium, long, and full-time formats. The locale identifies the format of times that conform to the standards of that nation.

The following table contains some examples of different date formats from different locales for each of the categories.

Locale identifier and description	Time format (Category)	Example
en_GB English (United Kingdom)	HH:MM (Short)	14:13
fr_CA French (Canada)	HH:MM:SS (Medium)	12:15:50
de_DE German (Germany)	HH:MM:SS z (Long)	14:13:13 -0400
fr_FR French (France)	HH ' h ' MM Z (Full)	14 h 13 GMT-04:00

Date and time picture formats

The following symbols must be used to create date and time patterns for date/time fields. Certain date symbols are only used in Chinese, Japanese, and Korean locales. These symbols are also specified below.

Note: The comma (,), dash (-), colon (:), slash (/), period (.), and space ( ) are treated as literal values and can be included anywhere in a pattern. To include a phrase in a pattern, delimit the text string with single quotation marks ('). For example, 'Your payment is due no later than' MM-DD-YY can be specified as the display pattern.

Date symbol	Description	Formatted value for English (USA) locale where the locale-sensitive input value is 1/1/08 (which is January 1, 2008)
`D`	1 or 2 digit (1-31) day of the month	1
`DD`	Zero-padded 2 digit (01-31) day of the month	01
`J`	1, 2, or 3 digit (1-366) day of the year	1
`JJJ`	Zero-padded, three-digit (001-366) day of the year	001
`M`	One- or two-digit (1-12) month of the year	1
`MM`	Zero-padded, two-digit (01-12) month of the year	01
`MMM`	Abbreviated month name	Jan
`MMMM`	Full month name	January
`E`	One-digit (1-7) day of the week, where (1=Sunday)	3 (because January 1, 2008 is a Tuesday)
`EEE`	Abbreviated weekday name	Tue (because January 1, 2008 is a Tuesday)
`EEEE`	Full weekday name	Tuesday (because January 1, 2008 is a Tuesday)
`YY`	Two-digit year, where numbers less than 30 are considered to fall after the year 2000 and numbers 30 and higher are considered to occur before 2000. For example, 00=2000, 29=2029, 30=1930, and 99=1999	08
`YYYY`	Four-digit year	2008
`G`	Era name (BC or AD)	AD
`w`	One-digit (0-5) week of the month, where week 1 is the earliest set of four contiguous days ending on a Saturday	1
`WW`	Two-digit (01-53) ISO-8601 week of the year, where week 1 is the week containing January 4	01

Several additional date patterns are available for specifying date patterns in Chinese, Japanese, and Korean locales.

Japanese eras can be represented by several different symbols. The final four era symbols provide alternative symbols to represent Japanese eras.

CJK date symbol	Description
`DDD`	The locale’s ideographic numeric valued day of the month
`DDDD`	The locale’s tens rule ideographic numeric valued day of the month
`YYY`	The locale’s ideographic numeric valued year
`YYYYY`	The locale’s tens rule ideographic numeric valued year
`g`	The locale’s alternate era name. For the current Japanese era, Heisei, this pattern displays the ASCII letter H (U+48)
`gg`	The locale’s alternate era name. For the current Japanese era, this pattern displays the ideograph that is represented by the Unicode symbol (U+5E73)
`ggg`	The locale’s alternate era name. For the current Japanese era, this pattern displays the ideographs that are represented by the Unicode symbols (U+5E73 U+6210)
`g`	The locale’s alternate era name. For the current Japanese era, this pattern displays the full width letter H (U+FF28)
`g g`	The locale’s alternate era name. For the current Japanese era, this pattern displays the ideograph that is represented by the Unicode symbol (U+337B)

Time symbol	Description	Locale-sensitive input value	Formatted value for English (USA) locale
`h`	One- or two-digit (1-12) hour of the day (AM/PM)	12:08 AM or 2:08 PM	12 or 2
`hh`	Zero-padded 2 digit (01-12) hour of the day (AM/PM)	12:08 AM or 2:08 PM	12 or 02
`k`	One- or two-digit (0-11) hour of the day (AM/PM)	12:08 AM or 2:08 PM	0 or 2
`kk`	Two-digit (00-11) hour of the day (AM/PM)	12:08 AM or 2:08 PM	00 or 02
`H`	One- or two-digit (0-23) hour of the day	12:08 AM or 2:08 PM	0 or 14
`HH`	Zero-padded, two-digit (00-23) hour of the day	12:08 AM or 2:08 PM	00 or 14
`K`	One- or two-digit (1-24) hour of the day	12:08 AM or 2:08 PM	24 or 14
`KK`	Zero-padded, two-digit (01-24) hour of the day	12:08 AM or 2:08 PM	24 or 14
`M`	One- or two-digit (0-59) minute of the hour Note: You must use this symbol with an hour symbol.	2:08 PM	8
`MM`	Zero-padded, two-digit (00-59) minute of the hour Note: You must use this symbol with an hour symbol.	2:08 PM	08
`S`	One- or two-digit (0-59) second of the minute Note: You must use this symbol with an hour and minute symbol.	2:08:09 PM	9
`SS`	Zero-padded, two-digit (00-59) second of the minute Note: You must use this symbol with an hour and minute symbol.	2:08:09 PM	09
`FFF`	Three- digit (000-999) thousandth of the second Note: You must use this symbol with an hour, minute, and seconds symbol.	2:08:09 PM	09
`A`	The part of the day that is from midnight to noon (AM) or from noon to midnight (PM)	2:08:09 PM	PM
`z`	ISO-8601 time-zone format (for example, `Z,` +0500, -0030, -01, +0100) Note: You must use this symbol with an hour symbol.	2:08:09 PM	-0400
`zz`	Alternative ISO-8601 time-zone format (for example, `Z,` +05:00, -00:30, -01, +01:00) Note: You must use this symbol with an hour symbol.	2:08:09 PM	-04:00
`Z`	Abbreviated time-zone name (for example, GMT, GMT+05:00, GMT-00:30, EST, PDT) Note: You must use this symbol with an hour symbol.	2:08:09 PM	EDT

Reserved symbols

The following symbols have special meanings and cannot be used as literal text.

Symbol	Description
`?`	When submitted, the symbol matches any one character. When merged for display, it becomes a space.
`*`	When submitted, the symbol matches 0 or Unicode white space characters. When merged for display, it becomes a space.
`+`	When submitted, the symbol matches one or more Unicode white space characters. When merged for display, it becomes a space.

Locales

For a list of supported languages, see Locales topic in the Using Designer guide.