Change the values of a date field using a SQL expression in ArcGIS Online

Summary

When publishing a layer from ArcGIS Pro to ArcGIS Online, the date field of the hosted feature layer in ArcGIS Online displays the dates earlier or later than the original dates. By design, ArcGIS Online converts and stores data in Coordinated Universal Time (UTC), allowing ArcGIS Online to convert the data to the local time zone. Refer to ArcGIS Online: Published date fields store time in UTC.

The image below shows a table with a DOB (date of birth) field of a feature layer in ArcGIS Pro.

The image below shows the DOB field values are displayed a day later than the original dates when published to ArcGIS Online.

An existing workaround requires specifying the time zone of the layer before publishing the layer from ArcGIS Pro to ArcGIS Online. This article provides an alternative workflow to change the date field values using SQL expression in the Field Calculator in ArcGIS Online. 

Note:
This workflow is not suitable for time-sensitive data as it may affect the time values of the updated field values.

Procedure

To change the date field values in ArcGIS Online, follow the steps below.

1. In ArcGIS Online, open the hosted feature layer attribute table from the item details page or in Map Viewer. Refer to ArcGIS Online: Show a table in Map Viewer.

2. Click the date field header, and select Calculate.

3. In the Calculate Field dialog box, select SQL.

4. Construct the SQL expression in the Calculate Field dialog box to subtract one day from the date field values.
   1. Under Fields, select Date. In this example, the date field is 'DOB.'
   2. Select the minus (-) operator.
   3. Type 1 in the expression box under DOB =.
   4. Click Validate to verify the expression.

Note:
In this example, - 1 is used to subtract one day from the field values. Refer to ArcGIS Online: SQL examples for more information on subtracting a different number of days from the date field.

5. Click CALCULATE.

The image below shows the date field displays the original dates in ArcGIS Online.