Use date fields

Whether you're displaying, calculating, or selecting date attributes, you must determine the appropriate way to work with date values in your database. Date values can be stored in fields of type Date, DateOnly, TimeOnly, and TimestampOffset. Time values can be stored as additional information in Date and TimestampOffset fields, and can be stored as separate values in a TimeOnly field.

Selecting and displaying date values using the Select Layer By Attribute tool and similar query-building dialog boxes is done using an SQL syntax. The SQL syntax used to select and display date values varies based on the type of database. This is described in detail in the SQL reference topic.

Calculating and displaying date fields in the attribute table is done in a different format and syntax. This is because the field calculator in ArcGIS AllSource uses Python functions and the display format depends on your regional settings, rather than the underlying database's format on the system.

Display dates

A shapefile stores dates in a Date field with this format: yyyy-mm-dd. A geodatabase stores date and time values in either Date, DateOnly, TimeOnly, or TimestampOffset fields. When using a Date field, it formats the date as datetime yyyy-mm-dd hh:mm:ss AM or PM, with DateOnly and TimeOnly fields using only the respective portion. TimestampOffset fields contain the same information as the Date filed, plus a time zone offset value (from UTC) in hours. Settings on your Windows system determine how the dates display in ArcGIS AllSource—M/d/yy, MM/dd/yy, yy/MM/dd, and so on. ArcGIS AllSource uses the system short date format (numerical) to display dates. While these are the default format, dates can also be formatted to display differently in both the table and a pop-up.

ArcGIS AllSource follows the Windows settings for date formats.

Note:

Time zone information for a date field in a web feature layer is displayed in the ToolTip for the field header as well as any calendar control when editing those values. By default, the value is set to coordinated universal time (UTC), unless a preferred time zone has been set. Editing the map or feature service settings, including time zone, must be done on the hosted side.

Filter using date fields

When the table properties have time defined on a date field, the table will automatically filter its records to only show the records that fall within the current time slider extent. As you step or play the time slider in the map view, only the records that fall within the current span display in the table.

Note:

Tables opened from a catalog view or the Catalog pane will not be impacted by an active time slider.

By default, the filter is on when time is enabled in the map. To see all the records in the table, you can turn off Filter By Time Filter By Time at the bottom of the table view. You can also access filter options for the table under the Table tab, in the Filter group.

Calculate date fields

When calculating date values, the field calculator uses Python datetime functions. Some of the functions support datetime yyyy-mm-dd hh:mm:ss AM or PM. However, values stored in DateOnly or TimeOnly fields will only support functions applicable to their type. For shapefiles, the time portion is truncated from the datetime value. For example, the datetime 2002-08-20 12:00:00 PM is stored in a shapefile as 2002-08-20.

You can use any Python datetime function to calculate a date. To calculate an attribute equal to the current time, use the today() function.

You can also use two digits for the year value when calculating dates. When you calculate a date such as date_field = dateserial(02,8,20) and specify two digits for the year (02 in this case), your system's settings control which century is used. You can adjust these settings through the region and time options in Windows.

Select features using a date field

You can build an SQL selection query for date fields, numeric fields, and string fields using the Select Layer By Attribute geoprocessing tool. You can select individual or multiple records by querying a dataset based on a date value. A data-dependent syntax is required for querying dates.

Refer to the SQL reference guide for a description of the different syntax and the specifications of querying dates.