Date Filters¶

Goal: Confidently filter date and date-time fields. Date syntax varies by data source — this page covers the most common.

Date literals by source¶

ArcGIS Pro's SQL changes depending on what your data lives in:

→ When in doubt, check Esri's SQL reference.

File geodatabase — most common¶

-- Exact day
SURVEY_DATE = timestamp '2024-06-01 00:00:00'

-- Range
SURVEY_DATE >= timestamp '2024-01-01 00:00:00'
  AND SURVEY_DATE <  timestamp '2025-01-01 00:00:00'

-- Last 30 days (relative — needs computation in arcpy/Python first)
-- See "Relative dates" below

Note the < for the end of a range — use less-than to exclude the next year's first day.

Shapefile¶

SURVEY_DATE = date '2024-06-01'
SURVEY_DATE >= date '2024-01-01' AND SURVEY_DATE < date '2025-01-01'

SQL Server geodatabase¶

[SURVEY_DATE] >= '2024-01-01' AND [SURVEY_DATE] < '2025-01-01'

PostgreSQL geodatabase¶

"survey_date" >= DATE '2024-01-01' AND "survey_date" < DATE '2025-01-01'

Common patterns¶

Exact day (file gdb)¶

EVENT_TIME >= timestamp '2024-06-01 00:00:00'
  AND EVENT_TIME <  timestamp '2024-06-02 00:00:00'

Whole month¶

EVENT_TIME >= timestamp '2024-06-01 00:00:00'
  AND EVENT_TIME <  timestamp '2024-07-01 00:00:00'

Whole year¶

EVENT_TIME >= timestamp '2024-01-01 00:00:00'
  AND EVENT_TIME <  timestamp '2025-01-01 00:00:00'

Before a cutoff¶

SUBMIT_DATE < timestamp '2024-12-31 23:59:59'

Relative dates ("last 30 days")¶

ArcGIS Pro's WHERE syntax doesn't have CURRENT_DATE for file gdb. To select the last 30 days dynamically, compute the cutoff in Python and pass it in:

import arcpy, datetime

cutoff = datetime.datetime.now() - datetime.timedelta(days=30)
where = f"EVENT_TIME >= timestamp '{cutoff:%Y-%m-%d %H:%M:%S}'"

arcpy.management.SelectLayerByAttribute(
    "events_layer", "NEW_SELECTION", where
)

For enterprise databases, you can use the database's date functions:

-- SQL Server
[event_time] >= DATEADD(day, -30, GETDATE())

-- PostgreSQL
"event_time" >= NOW() - INTERVAL '30 days'

-- Oracle
"event_time" >= SYSDATE - 30

Time-only or date-only fields¶

If your field is Date (date only — no time component), most sources accept just a date string. If it's DateTime (timestamp), include the time portion.

In ArcGIS Pro, check Fields View → field type:

• Date in file gdb is actually datetime under the hood. Always include time when querying.

Time zones¶

ArcGIS Pro file geodatabases store dates without timezone info by default. Be careful with cross-zone data — convert to UTC on import if you can.

NULLs¶

SURVEY_DATE IS NULL                -- never surveyed
SURVEY_DATE IS NOT NULL            -- has a survey date

Common mistakes¶

Practice¶

→ Next: ArcGIS Pro SQL Examples.