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. Show 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.
Date and time format stringsA date and time format string is a string of text used to interpret data values containing date and time information. 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
Customize date and time formatsA 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
SeparatorsSeparators 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:
LocalesLocales 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:
AM and PM designatorsStandard 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:
Related topics
Feedback on this topic? How do I convert a date into a Number in Excel?In the blank cell:. Enter =DATEVALUE(. Click the cell that contains the text-formatted date that you want to convert.. Enter ). Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date. What is an Excel serial number?. How do I convert a date to a general date in Excel?Select the cell or cells with serial numbers that you want to convert to dates. Locate the "Home" tab in the top right of the Excel window and click on it. Find the "Number Format" box in the toolbar that will usually say "General" or "Text." Click the down arrow next to the box and select "Date" from the menu.
How do I convert date to mm/dd/yyyy in Excel?In an Excel sheet, select the cells you want to format. Press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Custom from the Category list and type the date format you want in the Type box. Click OK to save the changes.
How do I convert a date to 8 digits in Excel?To do this, select a cell or a range of cells with the numbers you want to convert to dates and press Ctrl + 1 to open the Format Cells dialog box. On the Number tab, choose Date, select the desired date format in Type and click OK.
|