Fundamentals of date fields

Date fields vary between data source types, and their display depends on your system locale. Whether you're displaying, calculating, or selecting date attributes, you must determine the appropriate way to work with date fields in your database.

Selecting and displaying date field values on the Select by Attributes and similar query building dialog boxes is done using a SQL syntax that will vary depending on the underlying database or data format. 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 ArcMap uses Python and VBScript functions and the display format depends on the system, not the database. This topic only describes working with date fields in the attributes table and using the Calculate Field geoprocessing tool (launched from the Field Calculator command from the attribute table).

Displaying dates

A coverage or shapefile stores dates in a date field with this format: yyyy-mm-dd. A geodatabase formats the date as datetime yyyy-mm-dd hh:mm:ss AM or PM. Settings on your Windows system determine how the dates are displayed in ArcMap—M/d/yy, MM/dd/yy, yy/MM/dd, and so on. ArcMap uses the system short date format (numerical) for displaying dates.

Calculating date fields

When calculating date fields, the field calculator in ArcMap can use Python and VBScript datetime functions. Some of the functions support datetime yyyy-mm-dd hh:mm:ss AM or PM. However, for coverages and 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 coverage or shapefile as 2002-08-20.

Any Python datetime function can be used to calculate a date. If you want to calculate an attribute equal to current time, you can 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. This setting can be adjusted through Regional Options on Windows systems.

Learn about using two digits for the year value when calculating dates

Related Topics


9/17/2012