It is recommended that you store the time values of temporal data in one of the dedicated date field types. These database field types are specifically designed for storing time and date information. They are more efficient for query performance and support more sophisticated database queries than storing time in a numeric or string field.
Learn more about best practices for storing temporal data
If you have time values stored in a string or numeric (short, long, float, or double) field, you can convert them into a date field type using the Convert Temporal Field geoprocessing tool. Use this tool to specify a standard or custom time format for interpreting date and time values and converting those into one of the date formats.
Standard formats are supported with the Convert Temporal Field tool. However, if you have time values stored in a string field using a custom date and time format, you can create a custom date and time format to interpret your data. For example, when using the Convert Time Field tool to convert a time value of Tuesday, August 20, 2002, stored as a string into a date format, specify the input time format as dddd, MMMM dd, yyyy.
Note:
- When using the Convert Temporal Field tool, the list of standard supported input time formats varies depending on whether the input time values are stored in a string or numeric (short, long, float, or double) field.
- The Convert Temporal Field tool allows you to specify custom date and time formats only when the time values are stored in a string field. Custom date and time formats are not supported when time values are stored in numeric fields.
Date and time format strings
A date and time format string contains date and time information in a consistent format. Each format string consists of a combination of formats from an available format type. Some examples of format types are day of week, month, hour, and second. In a single date and time format string, only one format from each format type should be used. However, not every format type needs to be included in a format string. For example, it is common to define a date format string that contains only year, month, and day of month information, without including any information about the time of day.
A format string can contain time information only, date information only, or a combination of date and time information. Format strings may also include separators, such as commas, that separate the formats used in the format string.
You need to analyze your data to determine the appropriate date and time format string to interpret your data. The following examples show a variety of format strings for interpreting dates and times:
Examples of Date and Time Format Strings
| Example data value | Format string |
|---|---|
30/05/1978 02:34:56 | dd/MM/yyyy HH:mm:ss |
2/4/2010 2:39:28 PM | M/d/yyyy h:mm:ss tt |
6:05:12 a.m. | h:mm:ss tt |
23:31:18.345 | HH:mm:ss.s |
Tuesday, August 20, 2002 | dddd, MMMM dd, yyyy |
Wed, Aug 31 1994 | ddd, MMM dd yyyy |
03281999030456 | MMddyyyyHHmmss |
2015-07-04T13:11:26.002 | yyyy-MM-ddTHH:mm:ss.sss |
Customize date and time formats
A custom format string consists of one or more date and time format specifiers. The following table summarizes the format specifiers that can be used to create a custom date and time format for interpreting data:
Date and Time Format Specifiers
| Format specifier | Format type | Description |
|---|---|---|
d | Day of Month | Day of month as digits with no leading zero for single-digit days. |
dd | Day of Month | Day of month as digits with leading zero for single-digit days. |
ddd | Day of Week | Day of week as a three-letter abbreviation. The function uses the abbreviations associated with the specified locale, for example, Mon in English (United States). Tip:Days of the week are not verified when interpreting a date value; therefore, it doesn't matter whether a day of the week appearing in your data correctly corresponds to the date represented in the data value. |
dddd | Day of Week | Day of week as its full name. The function uses the full day names associated with the specified locale, for example, Monday in English (United States). Tip:Days of the week are not verified when interpreting a date value; therefore, it doesn't matter whether a day of the week appearing in your data correctly corresponds to the date represented in the data value. |
M | Month | Month as digits with no leading zero for single-digit months. |
MM | Month | Month as digits with leading zero for single-digit months. |
MMM | Month | Month as a three-letter abbreviation. The function uses the month abbreviations associated with the specified locale, for example, Nov in English (United States). |
MMMM | Month | Month as its full name. The function uses the full month names associated with the specified locale, for example, November for English (United States), and noviembre for Spanish (Spain). |
y | Year | Year as last two digits but with no leading zero for years whose last two digits are less than 10. Tip:Years represented in this way can range from 1950 to 2049. A value of 49 or less is interpreted as occurring in the 21st century, for example, 7 is interpreted as 2007. A value of 50 or greater is interpreted as occurring in the 20th century, for example, 67 is interpreted as 1967. |
yy | Year | Year represented by the last two digits but with a leading zero for years whose last two digits are less than 10. Tip:Years represented in this way can range from 1950 to 2049. A value of 49 or less is interpreted as occurring in the 21st century. For example, 07 is interpreted as 2007. A value of 50 or greater is interpreted as occurring in the 20th century, for example, 67 is interpreted as1967. |
yyy | Year | Year represented by three digits. Years represented in this way can range from 1 to 999. |
yyyy | Year | Year represented by four digits. |
gg | Era | Period/era string. The function uses the era values associated with the specified locale. |
T | Time Separator | A delimiter that concatenates a preceding date value and a following time value. The function is typically seen in ISO formats. |
h | Hour | Hour with no leading zero for single-digit hours; 12-hour clock. |
H | Hour | Hour with no leading zero for single-digit hours; 24-hour clock. |
hh | Hour | Hours with leading zero for single-digit hours; 12-hour clock. |
HH | Hour | Hours with leading zero for single-digit hours; 24-hour clock. |
m | Minute | Minutes with no leading zero for single-digit minutes. |
mm | Minute | Minutes with leading zero for single-digit minutes. |
s | Second | Seconds with no leading zero for single-digit seconds. |
ss | Second | Seconds with leading zero for single-digit seconds. |
s.s | Second | Seconds, including subseconds, with no leading zero for single-digit seconds. Although the format only shows one decimal place, any number of decimal places can be used. Caution:The precision of subsecond values is limited to what is supported for the time field data type. For example, if using a time field of field data type float or double, rounding errors should be expected. |
ss.s | Second | Seconds, including subseconds, with leading zero for single-digit seconds. Although the format only shows one decimal place, any number of decimal places can be used. Caution:The precision of subsecond values is limited to what is supported for the time field data type, for example, if using a time field of field data type float or double, rounding errors should be expected. |
t | Time Marker | One-character time marker string, such as A or P. |
tt | Time Marker | Multicharacter time marker string, such as AM or PM. |
z | Time Zone Designator | This suffix indicates that the time value is in coordinated universal time (UTC). The function is typically seen in ISO formats. |
Separators
Separators are characters that separate information in text data values. Examples of commonly used separators are commas (,), colons (:), and spaces ( ), but there are no restrictions on the separators that can be used to create format strings. You can create format strings without any separators at all, especially when interpreting dates and times stored in numeric fields because numeric fields cannot store most of the commonly used separator characters.
In rare cases, data may contain separators that conflict with the formats in the table above. In these cases, single quotation marks must be used to isolate separators within your format string. In general, single quotation marks can be used to isolate any separators within a format string, but it is not recommended that you use them unless there is a potential conflict. The following examples show these concepts:
| Example data value | Format | Date or time interpreted (displayed as MM/dd/yyyy or HH:mm:ss) |
|---|---|---|
month12day30year2010 | 'month'MM'day'dd'year'yyyy | 12/30/2010 |
30/12/2010 | ddMMyyyy | 12/30/2010 |
Time:18hr6min3sec | 'Time:'h'hr'm'min's'sec' | 18:06:03 |
18:6:3 | HH:m:s | 18:06:03 |
Locales
Locales are important because they determine the valid data values for the long representations of some of the date formats in the table above. For example, the value November is only interpreted correctly for MMMM if the locale is an English-language locale. In some cases, if the format string doesn't use any long representations, the locale is irrelevant for interpreting the date, but the locale may affect the interpretation of AM and PM designators. If no AM or PM designators are specified, the default AM and PM designators for the locale are used.
Locales do not affect the formats used to create format strings. For example, the character M (or MM, MMM, MMMM) represents months regardless of the locale. The following examples illustrate how the locale is used to interpret dates:
| Data value | Format string | Locale | Date interpreted (MM/dd/yyyy) |
|---|---|---|---|
November 30, 2010 | MMMM dd, yyyy | English (United States) | 11/30/2010 |
Noviembre 30, 2010 | MMMM dd, yyyy | Spanish (Spain) | 11/30/2010 |
Mon, Feb 22, 2010 | ddd, MMM dd, yyyy | English (United States) | 2/22/2010 |
30/12/2010 | dd/M/yyyy | All locales | 12/30/2010 |
AM and PM designators
Standard time markers, or AM and PM designators, exist for each locale. However, you can define your own time markers. If time markers exist in your format string (t or tt), the characters used to represent the time markers must be defined. Time markers are only appropriate for use with format strings using a 12-hour clock (h or hh). They are not appropriate for format strings using a 24-hour clock (H or HH). If you don't define your own time markers, the standard time markers for the selected locale are used. To define your own AM and PM designators, the time field must have a field data type of text. The following examples show these concepts using only a PM designator for simplicity. The same concepts apply to the AM designator:
| Data value | Format string | PM designator | Interpreted time (HH:mm:ss) |
|---|---|---|---|
6:12:34 P | h:mm:ss t | P | 18:12:34 |
6:12:34 p.m. | h:mm:ss tt | p.m. | 18:12:34 |
6:12:34 | H:mm:ss | PM | 6:12:34 |
6:12:34 PM | h:mm:ss tt | PM | 18:12:34 |