Converting string or numeric time values into date format
It is recommended that you store the time values of your temporal data in a date field. This is a special database field type specifically for storing time and date information. It is most efficient for query performance and supports more sophisticated database queries than when storing time in a numeric or string field.
If you have time values stored in a string or numeric field, you can choose to convert these string or numeric (short, long, float, or double) fields into a date field using the Convert Time Field geoprocessing tool. The Convert Time Field geoprocessing tool provides you with the flexibility to specify a standard or custom time format picture that is used for interpreting your date and time values and converting those into a date format for efficient use with ArcGIS. When storing time values in a string or numeric field, you should adhere to one of the standard supported string or numeric formats. However, there can be cases when you have time values stored in a string field using a custom time format such as Tuesday, August 20, 2002.
Note:ArcGIS Tracking Analyst also provides the same flexibility to specify a standard or custom time format picture for interpreting date and time values in your data. The concepts discussed here also apply if you are using the Tracking Analyst extension.
The standard formats that are supported with the Convert Time Field geoprocessing tool are easy to follow. However, if you have time values stored in a string field using a custom date and/or time format, you must understand the concepts below to create a custom date and/or time format picture that will be used to interpret your data. For example, when using the Convert Time Field geoprocessing tool to convert a time value of Tuesday, August 20, 2002 stored as a string into a date format, you should specify the input time format as dddd, MMMM dd, yyyy.
Note:- When using the Convert Time Field geoprocessing 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 Time Field geoprocessing tool allows you to specify custom date and/or time formats only when the time values are stored in a string field. Custom date and/or time formats are not supported when time values are stored in numeric fields.
Date and Time Format Pictures
A date and/or time format picture is a string of text that is used to interpret data values containing date and/or time information. Each format picture 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/or time format picture, only one format from each format type should be used. However, every format type does not need to be included in a format picture. For example, it is very common to define a date format picture that contains only Year, Month, and Day of Month information, without including any information about the time of day.
A format picture can contain only time information, only date information, or a combination of date and time information. Format pictures may also include separators, such as commas, that can be used to separate the formats used in the format picture.
Generally, you will need to analyze your custom data and determine the appropriate date and/or time format picture required for interpreting your data. The following examples are intended to give you an understanding of the variety of format pictures that can exist for interpreting dates and times in different formats.
Example data value | Format picture |
|---|---|
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 |
Date and Time Formats
The following table summarizes the formats that can be used to create date and/or time format pictures for interpreting your custom data.
Format | 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 less than 10. Tip:Years represented in this way can range from 1950 to 2049. A value of 49 or less will be interpreted as occurring in the 21st century, for example 2032. A value of 50 or greater will be interpreted as occurring in the 20th century, for example 1967. |
|
yy | Year |
Year represented by the last two digits, but with a leading zero for years less than 10. Tip:Years represented in this way can range from 1950 to 2049. A value of 49 or less will be interpreted as occurring in the 21st century, for example 2032. A value of 50 or greater will be interpreted as occurring in the 20th century, for example 1967. |
yyy | Year | Year represented by only 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. |
|
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. |
Separators
Separators are characters used to separate information in text data values. Examples of commonly used separators are commas (,), colons (:), and spaces ( ), but no restrictions are placed on the separators that can be used to create format pictures. It is also possible to create format pictures without any separators at all. This is usually the case when interpreting dates and times stored in numeric fields, because numeric fields cannot store most of the commonly used separator characters.
In rare cases, it is possible that data may contain separators that can conflict with the formats in the table above. In these rare cases, single quotes must be used to isolate separators within your format picture. In general, single quotes can be used to isolate any separators within a format picture, 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 picture | 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 will only be interpreted correctly for MMMM if the locale being used is an English language locale. In some cases, if the format picture being used doesn't use any long representations, the locale can become irrelevant for interpreting the date. However, it is important to note that the locale may still 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 will be used.
Locales do not affect the formats used to create format pictures. In other words, the character M (or MM, MMM, MMMM) is used to represent months regardless of the locale chosen. The following examples illustrate how the locale is used to interpret dates.
Data value | Format picture | 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, some of the tools available in ArcGIS provide you with the flexibility to define your own time markers. If time markers exist in your format picture (t or tt), then the characters used to represent the time markers must be defined. It is important to note that time markers are only appropriate for use with format pictures using a 12-hour clock (h or hh). They are not appropriate for format pictures 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 will be used. To define your own AM and PM designators, your 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 would apply to the AM designator.
Data value | Format picture | 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 |