Working with Date Fields in Field Calculator - Resource Centre

First thing first – I am not a developer. Sounds a simple enough thing, but sometimes, when I want to work with date fields in my feature classes, it feels like I need to be. If this statement resonates with you, then help is at hand. If you spend hours staring at the field calculator blankly, trying to work out what Date ( ) and Now ( ) actually mean, then I share your pain.

In my work, I frequently have to work with date and time data in tables; creating new data, reformatting existing data or performing analysis using this data. The trouble is, I always forget the correct expression required to perform some of these calculations, and as the required syntax is not always straight-forward, I usually end up pestering my colleagues who have developer experience to help me out.

And so, as an aid-memoir to me and hopefully for the benefit of a few of you, I have listed below some of the most common challenges I face and examples of the expressions required to get the required results. If this inspires you to try some more, then I have also listed some other resources (which I have used in preparing this list) at the bottom of this post.

Although my preference has been to write expressions in VB Script I’ve also included equivalent Python examples as well. Python is becoming more and more integrated within ArcGIS so if you’re not familiar with using it the examples I’ve included are an easy way to dip your toe in.

Example Data Calculations include:

The difference between Shapefiles and Geodatabase date fields – not an expression, but very useful to understand before you carry any out!
How to field-calculate today’s date
- As Date and Time
- As Date only
How to field-calculate a specific date
How to field-calculate random dates for a specific time period
How to convert dates in a String/Text field into a Date field

1. The difference between Shapefiles and Geodatabase date fields

One of the first things to be aware of is a subtle, yet crucial, difference between the way a shapefile and a geodatabase store date values.

A shapefile (shp) stores dates in a date field with this format: yyyy-mm-dd.
A geodatabase (gdb, mdb or sde) formats the date as datetime yyyy-mm-dd hh:mm:ss AM or PM.

Therefore if your data contains times, then you must use geodatabases (file, personal or SDE) or your times will be truncated to dates.

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. To alter your settings, go to Start>Control Panel>Region and Language

2. How to field-calculate today’s date

This may seem a simple task, but even this requires the correct Expression. These expressions may be used to populate a Date field or a Text field.

a. As Date and Time (for Geodatabases only – expression will work for shapefiles but will return the date only)

Using VB Script

MyField = Now ( )

Using Python

MyField = datetime.datetime.now( )

b. As Date only

Using VB Script

MyField = Date ( )

Using Python

MyField = time.strftime(“%d/%m/%Y “)

3. How to field-calculate a specific date

Sometimes, you have a specific date you want to populate numerous records with. To do this, you simply surround the date with symbols as per below:

Using VB Script

Date format: #DD-MM-YYYY# or #DD-MM-YYYY HH:MM:SS#

Example expressions:

e.g. for dates only:

MyField = #30-01-2012#

e.g. for dates and times:

MyField = #30-01-2012 12:35:15#

Using Python

Date format: “DD-MM-YYYY” or “DD-MM-YYYY HH:MM:SS”

Example expressions:

e.g. for dates only:

MyField = “30-01-2012”

e.g. for dates and times:

MyField = “30-01-2012 12:35:15”

4. How to field-calculate random dates for a specific time period

This may not be an everyday requirement, but it is something I need to do a lot when creating fictitious demo data. For example, the following code will create random dates between 01/01/2010 (inclusive) and 01/01/2011 (exclusive). In other words, the random dates go from 01/01/2010 to 31/12/2010, i.e. any date in 2010.

Using VB Script

Check “Show Codeblock”, and, in the “Pre-Logic Script Code” section, enter the following:

MinDate = #2010-01-01#
MaxDate = #2011-01-01#
Randomize
Dim MinDate, MaxDate, RandDate
RandDate = MinDate + INT((MaxDate – MinDate)*RND)

Edit the MinDate and MaxDate as appropriate. The MaxDate should always be the day after the maximum date you want to allow. So if you want to allow all dates in February 2010, “MinDate” should be 2010-02-01 and “MaxDate” should be 2010-03-01.

Then, beneath:

MyField = RandDate

Using Python

Check “Show Codeblock”, and, in the “Pre-Logic Script Code” section, enter the following:

import random
def randomDate(minDate, maxDate):
minDate = datetime.datetime.strptime(minDate, “%Y-%m-%d”)
maxDate = datetime.datetime.strptime(maxDate, “%Y-%m-%d”)
diff = (maxDate – minDate).days
return minDate + datetime.timedelta(random.randrange(0, diff))

Then, beneath:

randomDate(“2010-01-01”, “2011-01-01”)

5. How to convert dates in a String/Text field into a Date field

Often when data is provided to me or imported into ArcMap from Excel, dates are brought in as text fields rather than date field. On some occasions, the dates are nicely formatted in one field whilst other times, the date may be spread over a number of fields.

If the date is already in a correctly formatted (e.g. ‘30/01/2012’ or ’30-01-2012’ or ’30 Jan 2012’) then the field may be directly calculated by referencing the field only as follows:

Using VB Script

Expression:

MyField = [StringDateField]

Using Python

Expression:

MyField = !StringDateField!

If, however the required date elements are split over numerous text fields or an element of the date is missing, the following expression style may be used which forms the date by adding each date element together:

Using VB Script

Example input 1: 3 fields formatted as 30 | 01 | 2012 (Day|Month|Year)

MyField = [Day]&”-“& [Month]&”-“& [Year]

Example input 2: 2 fields formatted as 30 | 01 (Day|Month) with no year

let’s assume we want to set the year as 2009

MyField = [Day]&”-“& [Month]&”-“& 2009

Using Python

Example input 1: 3 fields formatted as 30 | 01 | 2012 (Day|Month|Year)

MyField = !Day! + “-” + !Month! + “-” + !Year!

Example input 2: 2 fields formatted as 30 | 01 (Day|Month) with no year

let’s assume we want to set the year as 2009

MyField = !Day! + “-” + !Month! + “-” + “2009”