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.

NoteNote:

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.

NoteNote:

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

Examples of Date and Time Format Pictures

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.

Date and Time Formats

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

TipTip:

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

TipTip:

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.

TipTip:

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.

TipTip:

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.

CautionCaution:

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.

CautionCaution:

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

Related Topics

3/5/2014