How to Extract Date from Datetime in SQL

To extract the date from a datetime value in SQL, use the expression for your database below. Each returns a date value rather than a formatted text label. Replace occurred_at with your timestamp column.

These expressions are useful in SELECT and daily GROUP BY queries. For a day filter, compare the original timestamp with the start of the day and the start of the next day. Also define which time zone owns that calendar day; otherwise, an event near midnight can land in the wrong bucket.

PostgreSQL: cast the timestamp to date

PostgreSQL accepts both standard CAST syntax and its :: shorthand:

SELECT occurred_at::date AS event_date
FROM events;

For daily counts, group by the same expression:

SELECT occurred_at::date AS event_date, COUNT(*) AS event_count
FROM events
GROUP BY occurred_at::date
ORDER BY event_date;

What event_date means depends on the column type. A timestamp without time zone contains date and clock-time fields without an inherent zone; casting it takes those fields' date. A timestamp with time zone (timestamptz) represents an instant, and PostgreSQL displays it in the session's TimeZone. Its date when cast therefore depends on that session setting. If a report must use a particular zone, convert explicitly before taking the date. The PostgreSQL date/time type documentation explains storage and session-zone behavior.

MySQL: use DATE()

MySQL's DATE() function extracts the date part of a date or datetime expression:

SELECT DATE(occurred_at) AS event_date
FROM events;

The MySQL date/time functions reference documents DATE(expr). You can also use CAST(occurred_at AS DATE). For daily totals, GROUP BY DATE(occurred_at) groups rows by the extracted value.

Check whether occurred_at is TIMESTAMP or DATETIME before interpreting that value. MySQL converts TIMESTAMP between UTC storage and the session time zone on retrieval; it does not do that automatic conversion for DATETIME. Thus DATE() on a TIMESTAMP can produce different calendar dates in sessions using different time zones. A DATETIME has no built-in zone to convert: its meaning comes from the convention used when writing it. The MySQL temporal type reference describes the distinction.

SQL Server: use CAST or CONVERT

SQL Server can convert a datetime or datetime2 value to its date component with either form:

SELECT CAST(occurred_at AS date) AS event_date
FROM events;

SELECT CONVERT(date, occurred_at) AS event_date
FROM events;

Use the form already familiar in your codebase. Both produce a date value, not a string representation. Microsoft's CAST and CONVERT documentation shows that conversion from datetime to date drops the time component. DATEPART(day, occurred_at) is different: it returns a day-of-month number such as 6, not a complete date such as 2024-05-06.

If the column is datetime2, its stored value has no offset. A datetimeoffset value includes an offset and can represent the same instant in different local clock times. For either type, choose the intended reporting zone before deciding which calendar date to display.

Filtering one day: use a half-open time range

Extracting a date in SELECT is not the same as choosing rows for that date in WHERE. For a day filter, include the first instant of the day and exclude the first instant of the next day:

WHERE occurred_at >= day_start
  AND occurred_at < next_day_start

day_start and next_day_start stand for typed values or bound parameters supplied by your application, not literal SQL keywords. A half-open range includes times like 23:59:59.999999 without guessing how much fractional-second precision the column stores. Adjacent daily ranges meet at exactly one boundary, so rows do not fall into two days.

Here is an executable SQL Server example for a datetime2 column whose stored clock times are UTC. The two variables mark consecutive UTC midnights:

DECLARE @day_start datetime2 = '2024-05-06T00:00:00';
DECLARE @next_day_start datetime2 = DATEADD(day, 1, @day_start);

SELECT occurred_at
FROM events
WHERE occurred_at >= @day_start
  AND occurred_at < @next_day_start;

The same comparison shape works in PostgreSQL and MySQL, but bind parameters using your driver and choose boundary types that match the column. In PostgreSQL, compare a timestamptz column to instants with time-zone meaning. In MySQL, keep the connection time zone consistent when comparing a TIMESTAMP column; for a UTC-by-convention DATETIME, bind UTC clock values. Do not paste SQL Server's @ variables into another database and expect identical syntax.

A predicate such as WHERE DATE(occurred_at) = ... or WHERE occurred_at::date = ... asks the database to derive a date from column values before comparing them. A range on the original timestamp is often easier to reuse with a normal timestamp index, but no query shape guarantees an index: the actual plan depends on the schema, indexes, statistics, casts, and optimizer. Check the execution plan if query cost matters. The half-open form is useful even without an index because its boundaries are precise.

Decide which time zone defines the date

An instant at 2024-05-06 00:30:00+00 is still May 5 in New York. Extracting its UTC date yields May 6; extracting its New York date yields May 5. Neither is inherently wrong—the report must state which day it means.

For a PostgreSQL timestamptz column, convert the instant to New York wall time before casting:

SELECT (occurred_at AT TIME ZONE 'America/New_York')::date AS local_date
FROM events;

Here AT TIME ZONE on a timestamptz returns the local clock time as timestamp without time zone, which can then be cast to date. Do not apply the same interpretation blindly to a timestamp without time zone: in that direction, PostgreSQL's AT TIME ZONE assumes the clock time belongs to the named zone and produces an instant. The two forms are documented in PostgreSQL's AT TIME ZONE reference.

If the report asks for all events on a New York calendar day, convert both local midnight boundaries to instants before filtering a timestamptz column. For May 6, 2024:

SELECT occurred_at
FROM events
WHERE occurred_at >= (TIMESTAMP '2024-05-06 00:00:00'
                      AT TIME ZONE 'America/New_York')
  AND occurred_at <  (TIMESTAMP '2024-05-07 00:00:00'
                      AT TIME ZONE 'America/New_York');

Computing each boundary separately matters around daylight-saving changes: a local calendar day need not contain exactly 24 elapsed hours. Adding a fixed 24 hours to the first UTC instant can select the wrong end for such a day.

For a MySQL DATETIME column that is known by convention to contain UTC clock values, convert before extracting a named local date:

SELECT DATE(CONVERT_TZ(occurred_at, '+00:00', 'America/New_York')) AS local_date
FROM events;

Named-zone conversion requires populated MySQL time-zone tables; otherwise CONVERT_TZ() may return NULL. If the column is TIMESTAMP, first account for the connection's time zone rather than treating its retrieved clock value as UTC. See the MySQL conversion reference and its TIMESTAMP rules.

For a SQL Server datetime2 column that is known to contain UTC clock values, attach UTC and then convert to the target Windows time-zone name:

SELECT CAST((occurred_at AT TIME ZONE 'UTC'
                         AT TIME ZONE 'Eastern Standard Time') AS date) AS local_date
FROM events;

If the source is already datetimeoffset, it already carries an offset: convert it to the target zone without first attaching UTC. Microsoft's AT TIME ZONE reference explains both cases and daylight-saving behavior. Despite the name, Eastern Standard Time is the SQL Server time-zone identifier used for the region's rules; it is not a request to force a fixed winter offset.

Keep date values separate from display strings

Use a real date value for grouping, joining, and passing data to application code. Format it as text only when you need a label in a particular presentation format. Similarly, EXTRACT(DAY FROM occurred_at) in PostgreSQL or MySQL, and DATEPART(day, occurred_at) in SQL Server, return the day number within a month. Grouping by that number alone would combine the sixth of different months.

The reliable sequence is: retain the full timestamp, define the reporting zone, extract a date for output or grouping, and filter with two correctly typed day boundaries. This answers the date-only question without losing events at the end of the day or silently changing which day a timestamp belongs to.