SQL Query to Convert Timestamp to Date: A Complete Guide for Every Database
Converting a timestamp to a date is one of the most common tasks developers and analysts face when working with SQL. Whether you are grouping sales by day, filtering logs by calendar date, or cleaning up data for a report, knowing how to strip the time portion from a TIMESTAMP or DATETIME value is essential. This guide walks you through the exact SQL query to convert timestamp to date across the most popular database systems, explains the logic behind each approach, and helps you choose the right method for your situation.
Why Convert a Timestamp to a Date?
A timestamp stores both a date and a time component, often down to fractions of a second. To give you an idea, 2026-09-21 10:20:32 is a full timestamp. Consider this: in many reporting and analytics scenarios, however, the time of day is irrelevant. You may only care that an event happened on September 21, 2026 Practical, not theoretical..
Common reasons to convert include:
- Grouping records by day for daily sales or traffic reports.
- Filtering data to a specific calendar date without worrying about the exact time.
- Deduplicating entries that occur multiple times on the same day.
- Joining tables where one table stores a full timestamp and another stores only a date.
- Improving query performance by comparing simpler date values instead of precise timestamps.
Understanding the conversion also helps you avoid a frequent bug: filtering with WHERE column = '2026-09-21' when the column contains a time component, which silently returns no rows.
Understanding the Data Types First
Before writing the query, it helps to know what you are working with. Most databases distinguish between these types:
DATE– stores only the year, month, and day (e.g.,2026-09-21).TIME– stores only hours, minutes, and seconds.DATETIME/TIMESTAMP– stores both date and time together.
The goal of conversion is to move from the third type to the first, discarding the time portion cleanly and predictably Less friction, more output..
SQL Query to Convert Timestamp to Date by Database
Because SQL is implemented differently across vendors, the function you use depends on your database engine. Below are the most widely used systems.
SQL Server
In SQL Server, the cleanest and most modern approach uses CAST or CONVERT:
-- Method 1: CAST (recommended, ANSI-standard)
SELECT CAST(SaleDate AS DATE) AS OnlyDate
FROM Orders;
-- Method 2: CONVERT with style
SELECT CONVERT(DATE, SaleDate) AS OnlyDate
FROM Orders;
Both return 2026-09-21 from a value like 2026-09-21 10:20:32. The CAST method is preferred because it is standard SQL and easy to read. Avoid older tricks like DATEADD(DATEDIFF(...)) unless you specifically need to work with older versions.
MySQL and MariaDB
MySQL offers several options. The most direct is the DATE() function:
-- Using the DATE() function
SELECT DATE(created_at) AS OnlyDate
FROM users;
-- Using CAST
SELECT CAST(created_at AS DATE) AS OnlyDate
FROM users;
-- Using EXTRACT
SELECT EXTRACT(DATE FROM created_at) AS OnlyDate
FROM users;
The DATE() function is the simplest and most readable choice in MySQL and MariaDB environments.
PostgreSQL
PostgreSQL supports the standard CAST syntax as well as a shorthand double-colon cast:
-- Standard CAST
SELECT CAST(order_ts AS DATE) AS OnlyDate
FROM orders;
-- Shorthand cast
SELECT order_ts::DATE AS OnlyDate
FROM orders;
PostgreSQL also lets you use DATE_TRUNC when you want to keep the result as a timestamp normalized to midnight:
SELECT DATE_TRUNC('day', order_ts) AS StartOfDay
FROM orders;
Note the difference: CAST(... AS DATE) returns a true date, while DATE_TRUNC returns a timestamp set to 00:00:00 Less friction, more output..
Oracle
In Oracle Database, a DATE value actually includes a time component, so the conversion behaves slightly differently. Use TRUNC to remove the time:
-- Remove the time portion
SELECT TRUNC(hire_date) AS OnlyDate
FROM employees;
-- Or use CAST
SELECT CAST(hire_date AS DATE) AS OnlyDate
FROM employees;
TRUNC is the idiomatic Oracle approach and is widely used in enterprise environments Small thing, real impact..
SQLite
SQLite stores dates as text, so you use the DATE() function to normalize the format:
SELECT DATE(created_at) AS OnlyDate
FROM events;
If the timestamp is stored as text with a time portion, DATE() extracts just the YYYY-MM-DD portion Simple, but easy to overlook..
Converting Timestamp to Date for Filtering
One of the most practical uses is filtering records to a single day. A common mistake is writing:
-- Works but can be slow and error-prone
SELECT * FROM orders
WHERE CAST(order_ts AS DATE) = '2026-09-21';
Wrapping the column in a function prevents the database from using an index, which can hurt performance on large tables. A more efficient, index-friendly pattern uses a range:
-- Sargable and index-friendly
SELECT * FROM orders
WHERE order_ts >= '2026-09-21'
AND order_ts < '2026-09-22';
This technique, often called writing a sargable query, tells the database it can safely use an index scan while still capturing every record on that day, regardless of the time value.
Grouping Records by Date
When building reports, you frequently need to aggregate values per day:
-- Daily sales totals (SQL Server / PostgreSQL syntax)
SELECT CAST(order_ts AS DATE) AS SaleDay,
COUNT(*) AS TotalOrders,
SUM(amount) AS Revenue
FROM orders
GROUP BY CAST(order_ts AS DATE)
ORDER BY SaleDay;
This groups all orders that occurred on the same calendar day, giving you one row per day with counts and totals. The same logic applies in MySQL and Oracle, adjusting the date function to match the dialect.
Formatting the Date Output
Sometimes you need the date in a specific display format rather than just a DATE value. Use the formatting function for your database:
- SQL Server:
FORMAT(order_ts, 'yyyy-MM-dd')orCONVERT(VARCHAR(10), order_ts, 23) - MySQL:
DATE_FORMAT(order_ts, '%Y-%m-%d') - PostgreSQL:
TO_CHAR(order_ts, 'YYYY-MM-DD') - Oracle:
TO_CHAR(order_ts, 'YYYY-MM-DD')
Formatting returns a string, so use it for display in reports rather than for further date arithmetic No workaround needed..
Handling Time Zones Correctly
A subtle but important consideration is time zones. A TIMESTAMP may be stored in UTC, while your users expect local time. That's why if you convert to a date before adjusting the time zone, you can end up on the wrong day. Take this: 2026-09-21 23:30 UTC is still September 21 in UTC, but it may already be September 22 in a timezone ahead of UTC.
To convert safely:
- First convert the timestamp to the desired time zone.
- Then extract the date portion.
In PostgreSQL, for instance:
SELECT (order_ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::DATE
FROM orders;
Always confirm whether your stored timestamps are in
UTC or your local time zone, and adjust accordingly to avoid off-by-one errors And that's really what it comes down to..
Conclusion
Mastering timestamp-to-date conversion is more than a syntactic detail—it's a foundation for reliable, high-performance applications. By favoring sargable range filters over function-wrapped columns, you preserve index efficiency. Grouping with CAST or DATE_TRUNC delivers clear daily aggregates, while formatting functions tailor output for reports without affecting underlying calculations. Most critically, applying time-zone conversions in the correct order prevents silent date shifts that could corrupt analytics. Together, these techniques ensure your queries are both fast and accurate, turning raw timestamps into trustworthy business intelligence Not complicated — just consistent..
Advanced Techniques for Date‑Level Aggregation
1. DATE_TRUNC and Modern RDBMS
If you’re using BigQuery, Snowflake, or Redshift, the idiomatic way to truncate a timestamp to a day is DATE_TRUNC. It is both sargable on many platforms (when the expression is on the left side of a comparison) and extremely readable:
-- BigQuery / Snowflake
SELECT DATE_TRUNC(order_ts, DAY) AS SaleDay,
COUNT(*) AS TotalOrders,
SUM(amount) AS Revenue
FROM orders
GROUP BY SaleDay
ORDER BY SaleDay;
In PostgreSQL you can achieve the same result with DATE_TRUNC(order_ts, 'day') (available in version 12+). For older versions, order_ts::date remains the concise option No workaround needed..
2. Dealing with Daylight‑Saving Transitions
When a timestamp falls on the day a region springs forward or falls back, the local‑time date can shift unexpectedly. The safest approach is to always work in UTC for aggregation, then convert to a user‑friendly time zone after the date extraction:
-- PostgreSQL: keep UTC for grouping, then label in EST
SELECT (order_ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::DATE AS SaleDayLocal,
COUNT(*) AS TotalOrders,
SUM(amount) AS Revenue
FROM orders
GROUP BY SaleDayLocal
ORDER BY SaleDayLocal;
If you must store timestamps in a local zone, document the convention and apply the same conversion pattern everywhere Practical, not theoretical..
3. Rolling Calculations with Window Functions
Often you need not just daily totals but also moving averages or cumulative sums. Combine the date‑truncation logic with a windowed aggregate:
-- SQL Server
WITH DailySales AS (
SELECT CAST(order_ts AS DATE) AS SaleDay,
SUM(amount) AS DailyRevenue
FROM orders
GROUP BY CAST(order_ts AS DATE)
)
SELECT SaleDay,
DailyRevenue,
AVG(DailyRevenue) OVER (ORDER BY SaleDay ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS Rolling7DayAvg,
SUM(DailyRevenue) OVER (ORDER BY SaleDay) AS CumulativeRevenue
FROM DailySales
ORDER BY SaleDay;
The same pattern works in MySQL (SUM(amount) OVER (…)) and PostgreSQL (SUM(amount) OVER (...)). Window functions are efficient because they operate on the already‑grouped result set, not on the raw fact table The details matter here..
4. Safe Conversion of Nullable Timestamps
When order_ts can be NULL, using CAST(NULL AS DATE) returns NULL silently, which may skew counts if you’re not careful. A defensive pattern is to filter out nulls early or treat them explicitly:
-- PostgreSQL
SELECT COALESCE(order_ts::date, '1970‑01‑01') AS SaleDay, ...
FROM orders
WHERE order_ts IS NOT NULL; -- exclude nulls from aggregation
Alternatively, you can preserve nulls and later COALESCE them in the reporting layer.
5. Performance‑Oriented Tips
| Goal | Recommendation |
|---|---|
| Index preservation | Avoid wrapping columns in functions when filtering, e.g.Because of that, , WHERE order_ts >= '2024‑01‑01' instead of WHERE CAST(order_ts AS DATE) >= '2024‑01‑01'. |
| Pre‑materialized daily aggregates | For high‑volume tables, consider a daily summary table (orders_daily) that is refreshed via an incremental ETL job. Queries then read from the summary, bypassing the need for GROUP BY on the fly. Plus, |
| Partition pruning | If you partition orders by the truncated date (ORDER_TS::date), the optimizer can prune partitions quickly. Ensure the partition expression matches the grouping logic. |
Not obvious, but once you see it — you'll see it everywhere Easy to understand, harder to ignore..
ANALYZE (PostgreSQL) or UPDATE STATISTICS (SQL Server) so the optimizer has accurate histograms on the date column. Stale statistics after a bulk load are a common cause of unexpectedly slow GROUP BY queries. |
| Avoid DISTINCT on dates | COUNT(DISTINCT order_ts::date) forces a sort/hash over the whole set. Prefer GROUP BY on the truncated expression, which the planner can often optimize better.
Worth pausing on this one.
Common Pitfalls
Even with the right function, a few traps catch people out:
- Timezone drift.
CAST(order_ts AS DATE)uses the session timezone. Two analysts in different regions can produce different "daily" totals from the same data. Pin the timezone explicitly, as shown earlier. - Midnight boundaries. A sale at
2024‑03‑15 23:59:59and one at2024‑03‑16 00:00:00land on different days. If your business day starts at, say, 4 a.m., a plain date cast will misattribute late‑night orders. Shift the timestamp before truncating in that case. - Type mismatches in joins. Joining a truncated date to a
DATEdimension table works, but joining a truncatedTIMESTAMPto aDATEcolumn can silently fail to match. Normalize both sides to the same type. - Daylight saving gaps.
AT TIME ZONEhandles DST transitions, but a naive local‑time cast does not. During the spring‑forward hour, some timestamps don't exist locally; during fall‑back, some occur twice. Rely on the database's timezone database rather than manual offset arithmetic.
Putting It Together
A dependable daily‑aggregation query typically layers these ideas: store in UTC, convert to the reporting timezone once, truncate to a date, group, and only then apply window functions over the compact result. To give you an idea, a production‑ready PostgreSQL query might look like:
WITH daily AS (
SELECT (order_ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York')::DATE AS sale_day,
COUNT(*) AS total_orders,
SUM(amount) AS revenue
FROM orders
WHERE order_ts >= '2024‑01‑01' -- sargable, index‑friendly
AND order_ts IS NOT NULL
GROUP BY sale_day
)
SELECT sale_day,
total_orders,
revenue,
AVG(revenue) OVER (ORDER BY sale_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM daily
ORDER BY sale_day;
This keeps the expensive work — scanning and grouping the raw fact table — to a minimum, and pushes the windowed math onto a small, already‑aggregated set But it adds up..
Conclusion
Truncating timestamps to dates is deceptively simple: the syntax is a one‑liner in every major dialect, but correctness depends on the details around it. Choose the function that matches your database (CAST, TRUNC, DATE, CONVERT, or DATE_TRUNC), store timestamps in UTC to avoid ambiguity, and convert to the reporting timezone deliberately rather than implicitly. Practically speaking, watch the midnight and DST boundaries that quietly shift your numbers, guard against NULL values, and keep your queries index‑friendly by filtering on the raw column instead of a wrapped expression. Day to day, for high‑volume tables, materialize daily aggregates so reporting reads from a small summary rather than re‑grouping the fact table on every request. Get these fundamentals right and daily aggregation becomes a reliable, performant building block rather than a recurring source of subtle reporting bugs Not complicated — just consistent..