Ian Klosowicz
Dates and times are in almost every dataset analysts work with: order timestamps, user signup dates, session durations, cohort windows, churn dates. If you can't manipulate date columns in SQL, a huge portion of real analysis is off the table. This guide covers the functions and patterns that come up most often in actual analyst work, with examples you can adapt directly.
One caveat up front: date and time syntax varies more across SQL databases than almost anything else in the language. The concepts are consistent, but function names and formatting differ between PostgreSQL, MySQL, SQL Server, BigQuery, Snowflake, and Redshift. Where there are meaningful differences, this guide calls them out.
Date handling shows up in almost every analyst task, which is part of how much SQL an entry-level role actually expects.
The first thing most date-based queries need is a reference point: today's date or the current timestamp. Every major SQL database has a function for this, but the names differ.
In practice, use whichever your database supports and be consistent. Mixing NOW() and CURRENT_TIMESTAMP in the same codebase creates confusion for anyone reading your queries later.
Example: Find users who signed up in the last 30 days.
-- PostgreSQL / Snowflake / Redshift
SELECT user_id, signup_date
FROM users
WHERE signup_date >= CURRENT_DATE - INTERVAL '30 days';
EXTRACT and DATEPART let you pull a specific component out of a date column: the year, month, day, hour, day of week, and so on. This is useful for grouping by month, filtering by day of week, or building time-based features.
Standard SQL (PostgreSQL, Snowflake, Redshift, BigQuery):
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DOW FROM order_date) AS day_of_week -- 0=Sunday in PostgreSQL
FROM orders;
SQL Server:
SELECT
DATEPART(YEAR, order_date) AS order_year,
DATEPART(MONTH, order_date) AS order_month,
DATEPART(WEEKDAY, order_date) AS day_of_week
FROM orders;
MySQL:
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAYOFWEEK(order_date) AS day_of_week
FROM orders;
BigQuery also supports dedicated functions: EXTRACT(YEAR FROM date), DATE_TRUNC, and FORMAT_DATE, which are often cleaner than generic EXTRACT for analytical queries.
Day-of-week numbering is one of the most common gotchas. PostgreSQL's DOW starts at 0 for Sunday. SQL Server's WEEKDAY starts at 1 for Sunday by default (but can be changed with SET DATEFIRST). MySQL's DAYOFWEEK also starts at 1 for Sunday. Always verify which day is 1 in your database before building day-of-week filters or reports.
Adding and subtracting intervals from dates is one of the most frequent operations in analytical SQL. The syntax splits into 2 main approaches: INTERVAL syntax and database-specific functions.
INTERVAL syntax (PostgreSQL, MySQL, Snowflake, Redshift, BigQuery):
-- 30 days ago
SELECT CURRENT_DATE - INTERVAL '30 days';
-- 3 months from a date column
SELECT order_date + INTERVAL '3 months' AS expected_renewal
FROM subscriptions;
-- 1 year ago
SELECT CURRENT_DATE - INTERVAL '1 year';
SQL Server uses DATEADD:
-- 30 days ago
SELECT DATEADD(DAY, -30, GETDATE());
-- 3 months from a date column
SELECT DATEADD(MONTH, 3, order_date) AS expected_renewal
FROM subscriptions;
INTERVAL syntax is more readable and closer to plain English. DATEADD is SQL Server-specific but unavoidable if that's your database. BigQuery also accepts DATE_ADD and DATE_SUB as alternatives to INTERVAL arithmetic.
One thing that trips people up: month arithmetic doesn't always behave the way you'd expect at month boundaries. Adding 1 month to January 31 gives February 28 or 29 (depending on the year), not March 2 or 3. Most databases handle this by clamping to the last valid day of the resulting month, but it's worth testing if your analysis depends on exact month-end dates.
Calculating how many days, months, or years separate 2 dates is a core analytical operation: customer tenure, days since last purchase, trial length, time-to-close. The function is DATEDIFF in most databases but works differently in each.
SQL Server and MySQL:
-- Days between signup and first purchase
SELECT
user_id,
DATEDIFF(DAY, signup_date, first_purchase_date) AS days_to_convert
FROM users;
-- MySQL uses a different argument order: DATEDIFF(end, start)
SELECT DATEDIFF(first_purchase_date, signup_date) AS days_to_convert
FROM users;
PostgreSQL: subtract dates directly, which returns an integer in days.
SELECT
user_id,
(first_purchase_date - signup_date) AS days_to_convert
FROM users;
Snowflake / Redshift:
SELECT
user_id,
DATEDIFF('day', signup_date, first_purchase_date) AS days_to_convert
FROM users;
BigQuery:
SELECT
user_id,
DATE_DIFF(first_purchase_date, signup_date, DAY) AS days_to_convert
FROM users;
The argument order (start vs end first) is the biggest source of errors here. A negative result usually means you put the arguments in the wrong order. Always sanity-check with a row where you know the answer before running the query at scale.
I've debugged DATEDIFF argument-order bugs more times than I'd like to admit, including on pipelines that had been running incorrectly for months before anyone noticed. It's the kind of silent error that skews cohort analyses without throwing a visible error.
Date truncation rounds a timestamp down to the start of a period: the start of the week, month, quarter, or year. This is the foundation of time-series reporting because it lets you GROUP BY a consistent period label without losing rows to timestamp precision.
PostgreSQL / Redshift:
-- Group by month
SELECT
DATE_TRUNC('month', order_date) AS order_month,
COUNT(*) AS orders,
SUM(revenue) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;
BigQuery:
SELECT
DATE_TRUNC(order_date, MONTH) AS order_month,
COUNT(*) AS orders,
SUM(revenue) AS monthly_revenue
FROM orders
GROUP BY order_month
ORDER BY order_month;
SQL Server uses DATETRUNC (SQL Server 2022+) or a workaround:
-- Pre-2022 workaround: cast to start of month
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0) AS order_month,
COUNT(*) AS orders
FROM orders
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, order_date), 0);
Snowflake:
SELECT
DATE_TRUNC('month', order_date) AS order_month,
COUNT(*) AS orders
FROM orders
GROUP BY order_month;
DATE_TRUNC is the function you'll use more than almost any other for reporting work. A common pattern is to pair it with a WHERE clause that filters to a rolling window: the last 12 months, last 90 days, current quarter. This gives you trend charts that update automatically without hardcoded date ranges.
Date handling is a constant in the SQL analysts write day to day, especially for cohort and trend work.
Sometimes you need a date column formatted as a readable string: "September 2026" for a report header, "2026-09" for a chart axis label, "Mon 14" for a daily breakdown. TO_CHAR and FORMAT handle this in most databases.
PostgreSQL / Redshift:
SELECT TO_CHAR(order_date, 'YYYY-MM') AS year_month,
TO_CHAR(order_date, 'Month YYYY') AS month_label,
TO_CHAR(order_date, 'Day') AS day_name
FROM orders;
MySQL:
SELECT DATE_FORMAT(order_date, '%Y-%m') AS year_month,
DATE_FORMAT(order_date, '%M %Y') AS month_label,
DATE_FORMAT(order_date, '%W') AS day_name
FROM orders;
SQL Server:
SELECT FORMAT(order_date, 'yyyy-MM') AS year_month,
FORMAT(order_date, 'MMMM yyyy') AS month_label,
DATENAME(WEEKDAY, order_date) AS day_name
FROM orders;
BigQuery:
SELECT FORMAT_DATE('%Y-%m', order_date) AS year_month,
FORMAT_DATE('%B %Y', order_date) AS month_label,
FORMAT_DATE('%A', order_date) AS day_name
FROM orders;
Keep formatted strings out of WHERE clauses and GROUP BY logic. Format at the final SELECT stage after all filtering and grouping is done on the actual date column. Filtering on a formatted string means the database can't use an index on the date column.
Raw data often lands in a table with dates stored as strings: "2026-09-12", "09/12/2026", "September 12, 2026". Before you can do any date arithmetic, you have to cast these to an actual date type.
CAST and CONVERT are the standard approaches:
-- Standard SQL (works in most databases)
SELECT CAST('2026-09-12' AS DATE) AS parsed_date;
-- SQL Server also supports CONVERT
SELECT CONVERT(DATE, '2026-09-12', 23) AS parsed_date;
-- The third argument (23) specifies the input format: 23 = yyyy-mm-dd
PostgreSQL:
SELECT '2026-09-12'::DATE AS parsed_date;
SELECT TO_DATE('12/09/2026', 'DD/MM/YYYY') AS parsed_date;
BigQuery:
SELECT PARSE_DATE('%Y-%m-%d', '2026-09-12') AS parsed_date;
SELECT PARSE_DATE('%m/%d/%Y', '09/12/2026') AS parsed_date;
Snowflake:
SELECT TO_DATE('2026-09-12', 'YYYY-MM-DD') AS parsed_date;
SELECT TRY_TO_DATE('bad-value', 'YYYY-MM-DD') AS parsed_date; -- returns NULL instead of error
TRY_TO_DATE (Snowflake) and TRY_CAST (SQL Server) are useful when source data is messy and you don't want the query to fail on a bad row. They return NULL for unparseable values instead of throwing an error. Use them when data quality is uncertain; use the strict versions when you want errors to surface immediately.
Time zones become important the moment your data involves users or events across multiple regions, or when timestamps are stored in UTC and need to be displayed in local time. Most analytical databases store timestamps in UTC by default.
PostgreSQL:
-- Convert a UTC timestamp to US Eastern time
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS eastern_time
FROM events;
-- Current timestamp in a specific zone
SELECT NOW() AT TIME ZONE 'America/Los_Angeles' AS pacific_now;
BigQuery:
SELECT DATETIME(created_at, 'America/New_York') AS eastern_time
FROM events;
Snowflake:
SELECT CONVERT_TIMEZONE('UTC', 'America/New_York', created_at) AS eastern_time
FROM events;
SQL Server:
SELECT created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS eastern_time
FROM events;
The practical rule for most analysts: store and compute in UTC, convert to local time only at the display layer. Doing time zone math mid-query across aggregations creates subtle errors, especially around daylight saving time transitions where an hour repeats or skips.
These are the date query structures that come up repeatedly in real analysis work.
Rolling 90-day window:
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
AND order_date < CURRENT_DATE;
Month-over-month revenue comparison:
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(revenue) AS monthly_revenue
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '13 months'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
Cohort assignment by signup month:
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;
Days since last activity (recency):
SELECT
user_id,
MAX(activity_date) AS last_active,
CURRENT_DATE - MAX(activity_date) AS days_since_active
FROM user_activity
GROUP BY user_id;
Filtering to business days (approximate, excludes weekends):
-- PostgreSQL: DOW 0=Sunday, 6=Saturday
SELECT *
FROM events
WHERE EXTRACT(DOW FROM event_date) NOT IN (0, 6);
Year-to-date filter:
SELECT *
FROM orders
WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)
AND order_date < CURRENT_DATE;
I built most of the reporting infrastructure at my job on patterns like these. The month-over-month and cohort structures in particular are in virtually every analytics stack I've worked with. Getting them right in SQL, rather than post-processing in a BI tool, keeps the logic in one place and makes pipelines faster to maintain.
If you want to build these skills as part of a structured path into data analytics, Analyst Hive covers the SQL patterns that actually appear in analyst roles and applies them in portfolio projects.
Why do date functions vary so much between SQL databases?
The SQL standard defines date and time types but is vague on functions. Each database vendor implemented their own extensions over decades before much standardization happened. The core concepts (extract, truncate, add intervals, format) are universal, but the function names and syntax reflect each database's independent evolution. This is one of the strongest arguments for learning the concepts first and looking up the syntax for your specific database as needed.
What's the difference between DATE, DATETIME, and TIMESTAMP?
DATE stores only the calendar date (year, month, day) with no time component. DATETIME stores both date and time but no time zone information. TIMESTAMP also stores date and time, and in many databases (PostgreSQL, MySQL) it includes or assumes a time zone. In practice, use DATE when you only care about the day, TIMESTAMP when you need precision to the second or sub-second and may need time zone handling.
How do I filter for the current month in SQL?
The cleanest approach is to use DATE_TRUNC or its equivalent to get the start of the current month, then filter from there: WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE). This avoids hardcoding month numbers and works correctly at year boundaries.
Why does my date subtraction return a decimal instead of whole days?
This usually means you're subtracting TIMESTAMP columns rather than DATE columns. Timestamps include the time component, so the difference includes fractional days. Cast both values to DATE first, or use DATEDIFF with the 'day' interval to force whole-day arithmetic.
How do I get the first and last day of a month in SQL?
First day: DATE_TRUNC('month', your_date) in PostgreSQL/Snowflake, or DATEADD(MONTH, DATEDIFF(MONTH, 0, your_date), 0) in SQL Server. Last day: PostgreSQL has LAST_DAY in some versions; a reliable cross-database approach is to get the first day of the next month and subtract 1 day.
What's the best way to group data by week?
DATE_TRUNC('week', date_column) in PostgreSQL and Snowflake, DATE_TRUNC(date_column, WEEK) in BigQuery. SQL Server requires a DATEADD/DATEDIFF workaround. One gotcha: most databases start the week on Monday for DATE_TRUNC, but EXTRACT(DOW) starts on Sunday. Verify which day your week anchor is before building weekly reports.
Date and time manipulation is in almost every real analysis query. The core operations are getting the current date, extracting parts, doing date arithmetic, calculating differences, truncating for period grouping, and formatting for display. The concepts are consistent across databases; the syntax is not. Learn the pattern, then look up the exact function name for your database.
DATE_TRUNC for period grouping and DATEDIFF for calculating durations are the 2 you'll use most. Get those right and the rest follows naturally.
If you're building SQL skills for a data analyst role, join Analyst Hive. The program focuses on the SQL you'll actually use at work, applied to portfolio projects that demonstrate real analytical thinking.
