Convert 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. A date field is a database field type specifically for storing time and date information. It is more efficient for query performance and supports more sophisticated database queries than storing time in a numeric or string field.

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 using the Convert Time Field geoprocessing tool. Use this tool to specify a standard or custom time format for interpreting date and time values and converting those into a date format.

Standard formats are supported with the Convert Time 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 Time 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 Time 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 valueFormat 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

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 specifierFormat typeDescription

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

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 valueFormatDate 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 valueFormat stringLocaleDate 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 valueFormat stringPM designatorInterpreted 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