Invalid Date / Datetime Format in SOQL

What does this error mean?

SOQL requires date and datetime values to follow ISO 8601 format strictly. Salesforce does not accept MM/DD/YYYY, DD-MM-YYYY, or other regional date formats commonly used in applications. When a non-conforming date string appears in a WHERE clause, the query parser rejects it before execution.

SOQL also provides a rich set of date literals (like TODAY, LAST_N_DAYS:30, THIS_MONTH) that are often preferable to hardcoded date values because they adapt automatically to the user's locale and time zone.

Common Causes

1. Using regional date format

Writing WHERE CloseDate = '01/15/2024' or WHERE CloseDate = '15-01-2024' — SOQL only accepts YYYY-MM-DD.

2. Invalid datetime timezone offset

Datetime values must include a valid timezone offset or Z (UTC). 2024-01-15T10:00:00 without a timezone is invalid; 2024-01-15T10:00:00Z or 2024-01-15T10:00:00-05:00 are correct.

3. Out-of-range values

Month 13, day 32, hour 25 — any out-of-range component produces this error even if the format is otherwise correct.

How to Fix It

Solution 1: Use correct ISO 8601 format

-- ❌ Invalid formats
WHERE CloseDate = '01/15/2024'
WHERE CreatedDate > '2024-01-15T10:00:00'   -- missing timezone

-- ✅ Correct formats
WHERE CloseDate = 2024-01-15             -- Date: no quotes, no timezone
WHERE CreatedDate > 2024-01-15T10:00:00Z  -- Datetime: Z for UTC
WHERE CreatedDate > 2024-01-15T10:00:00-05:00 -- Datetime with offset

Solution 2: Use SOQL date literals

Date literals are relative to the current date and automatically respect user timezone settings — far more robust than hardcoded values.

-- Common date literals
WHERE CreatedDate = TODAY
WHERE CloseDate = THIS_MONTH
WHERE CreatedDate = LAST_N_DAYS:30
WHERE CloseDate >= LAST_QUARTER
WHERE LastModifiedDate = LAST_N_HOURS:48
WHERE CreatedDate = THIS_FISCAL_YEAR

Solution 3: Format dates correctly in Apex

// Format an Apex Date for SOQL string
Date d = Date.today().addDays(-30);
String dateStr = d.format(); // 'YYYY-MM-DD' in Apex
String soql = 'SELECT Id FROM Opportunity WHERE CloseDate >= ' + dateStr;

// Or use a bind variable (preferred — no formatting needed)
Date cutoff = Date.today().addDays(-30);
List<Opportunity> opps = [
    SELECT Id FROM Opportunity
    WHERE CloseDate >= :cutoff
];