SQL Date & Time Functions: Cheat Sheet for Data Analysts

Introduction

Analysts live in dates. Nearly every business metric — daily active users, monthly revenue, weekly retention, quarter-over-quarter growth — is a date-based calculation. And nearly every SQL query you write as an analyst will involve extracting parts of a date, truncating to a month, adding or subtracting intervals, or comparing dates across rows.

The trouble is that date functions are the single most dialect-inconsistent part of SQL. MySQL, PostgreSQL, SQL Server, BigQuery, and Snowflake each have their own syntax for the same operation. A query that works perfectly in MySQL will error out in PostgreSQL, and vice versa. This cheat sheet gives you the essential date and time functions every data analyst needs, with side-by-side syntax across the major dialects. If you are working through a data analytics course in Bangalore, keep this open in a tab — it will save you hours of stack overflow searches. For a broader overview of database querying, check out our complete SQL for data analytics guide.

Getting the Current Date and Time

Every dialect has a function to return "now."

MySQL: NOW(), CURDATE(), CURTIME()
PostgreSQL: NOW(), CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
SQL Server: GETDATE(), SYSDATETIME()
BigQuery: CURRENT_DATETIME(), CURRENT_DATE(), CURRENT_TIMESTAMP()
Snowflake: CURRENT_TIMESTAMP(), CURRENT_DATE()

SELECT NOW(); -- MySQL, PostgreSQL
SELECT GETDATE(); -- SQL Server
SELECT CURRENT_TIMESTAMP(); -- BigQuery, Snowflake

Use these for filtering "today's data," calculating "days since signup," or timestamping rows in an INSERT. Remember that the exact type returned (DATE, TIMESTAMP, DATETIME) varies by dialect and affects how it can be used later.

Extracting Parts of a Date

You will constantly need to pull the year, month, day, hour, or day of week from a timestamp. The most portable syntax is EXTRACT, which works in PostgreSQL, BigQuery, Snowflake, and modern MySQL:

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

MySQL also supports shortcut functions: YEAR(order_date), MONTH(order_date), DAY(order_date), HOUR(order_date), DAYOFWEEK(order_date), DAYOFYEAR(order_date), WEEK(order_date).

SQL Server uses DATEPART: DATEPART(YEAR, order_date), DATEPART(MONTH, order_date).

BigQuery also supports EXTRACT and provides shortcuts like EXTRACT(DAYOFWEEK FROM order_date), where Sunday is 1.

For grouping by year or month in a report, extraction is often what you want.

Truncating to a Period: DATE_TRUNC

Truncating a date to the start of its month, week, or quarter is one of the most common analyst operations. It is how you build monthly reports from daily data.

PostgreSQL, BigQuery, Snowflake, Redshift: DATE_TRUNC('month', order_date)
MySQL: No native DATE_TRUNC; use DATE_FORMAT(order_date, '%Y-%m-01') or the newer DATE_FORMAT approaches.
SQL Server: DATETRUNC(month, order_date) in SQL Server 2022+, otherwise DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1).

SELECT DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY order_month;

DATE_TRUNC accepts 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute'. It is the single most useful date function for period-based reporting, and any analyst working in PostgreSQL or a modern cloud warehouse will use it daily.

If you are in MySQL, the DATE_FORMAT workaround is verbose but works reliably. Any data analytics course in Bangalore that teaches on MySQL will show you this workaround, so it is worth memorizing. For more on grouping and aggregating aggregated results, see our tutorial on SQL GROUP BY and HAVING.

Adding and Subtracting Intervals

Calculating "30 days ago," "one month from now," or "the same day last year" requires interval arithmetic.

MySQL: DATE_ADD(order_date, INTERVAL 30 DAY), DATE_SUB(order_date, INTERVAL 1 MONTH)
PostgreSQL: order_date + INTERVAL '30 days', order_date - INTERVAL '1 month'
SQL Server: DATEADD(DAY, 30, order_date), DATEADD(MONTH, -1, order_date)
BigQuery: DATE_ADD(order_date, INTERVAL 30 DAY), DATE_SUB(order_date, INTERVAL 1 MONTH)
Snowflake: DATEADD(DAY, 30, order_date), DATEADD(MONTH, -1, order_date)

Common analyst use: "orders from the last 30 days."

-- MySQL / BigQuery
SELECT * FROM orders WHERE order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);
-- PostgreSQL
SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
-- SQL Server
SELECT * FROM orders WHERE order_date >= DATEADD(DAY, -30, GETDATE());

Every dashboard you will ever build has a version of this filter.

Calculating Differences Between Dates

Analysts constantly need to know how many days, months, or years lie between two dates — days since signup, months of tenure, age of an outstanding invoice.

MySQL: DATEDIFF(end_date, start_date) returns days. TIMESTAMPDIFF(UNIT, start, end) handles other units.
PostgreSQL: end_date - start_date returns an integer number of days for DATE types; use AGE() or EXTRACT(EPOCH FROM ...) for finer control.
SQL Server: DATEDIFF(DAY, start_date, end_date), DATEDIFF(MONTH, start_date, end_date), etc.
BigQuery: DATE_DIFF(end_date, start_date, DAY), DATE_DIFF(end_date, start_date, MONTH).
Snowflake: DATEDIFF(DAY, start_date, end_date).

Real example — customer tenure in days:

-- BigQuery
SELECT customer_id, DATE_DIFF(CURRENT_DATE(), signup_date, DAY) AS tenure_days FROM customers;

Watch out for MySQL's DATEDIFF, which takes end first and start second — the opposite order of most other dialects. This is a classic bug source when moving queries between systems.

Formatting Dates for Display

Reports often need dates in a specific string format for readability or downstream systems.

MySQL: DATE_FORMAT(order_date, '%Y-%m-%d') for '2026-07-23', '%d-%b-%Y' for '23-Jul-2026'.
PostgreSQL: TO_CHAR(order_date, 'YYYY-MM-DD'), TO_CHAR(order_date, 'DD-Mon-YYYY').
SQL Server: FORMAT(order_date, 'yyyy-MM-dd'), or the older CONVERT with style codes.
BigQuery: FORMAT_DATE('%Y-%m-%d', order_date), FORMAT_DATE('%B %Y', order_date) for 'July 2026'.
Snowflake: TO_CHAR(order_date, 'YYYY-MM-DD').

For most analytics work, keep dates as DATE or TIMESTAMP types until the very last step and only format them at the end for presentation. Formatting early turns your dates into strings, which cannot be sorted or filtered correctly.

Converting Between Time Zones

Time zones are the source of some of the most infuriating data bugs. If your database stores UTC but your business reports in IST, forgetting to convert will silently offset all your daily numbers by five and a half hours.

PostgreSQL: order_time AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Kolkata'
MySQL: CONVERT_TZ(order_time, 'UTC', 'Asia/Kolkata')
BigQuery: DATETIME(order_time, 'Asia/Kolkata')
Snowflake: CONVERT_TIMEZONE('UTC', 'Asia/Kolkata', order_time)

Always know what time zone your source data is in, what time zone your business reports in, and convert deliberately. A large fraction of "why does this number look weird on Mondays?" bugs trace back to a missing time zone conversion. This is exactly the kind of subtle, career-affecting detail that a thorough data analytics course in Bangalore should cover in its SQL module.

The Week Definition Trap

Different dialects and cultures start the week on different days. In India and much of the world, week starts on Monday; in the US, on Sunday.

MySQL's WEEK() function has a mode argument that controls this. PostgreSQL's DATE_TRUNC('week', date) uses Monday as the first day (ISO 8601). BigQuery lets you pass WEEK(MONDAY) or WEEK(SUNDAY).

If your weekly numbers do not match what a colleague built, the week-start definition is the first thing to check. Make the definition explicit in your query so future readers know:

SELECT DATE_TRUNC('week', order_date) AS week_start, COUNT(*) FROM orders GROUP BY 1;

The same rule applies to fiscal years — if your company's fiscal year starts in April, plain YEAR() will not give you what leadership wants without an adjustment.

Real Analyst Queries Using Dates

Here are date patterns you will use every week on the job.

Daily active users this month: WHERE event_date >= DATE_TRUNC('month', CURRENT_DATE)

Same-day-last-year comparison: WHERE order_date = CURRENT_DATE - INTERVAL '1 year'

Rolling 28-day metrics: WHERE event_date BETWEEN CURRENT_DATE - INTERVAL '27 days' AND CURRENT_DATE

Cohort assignment: DATE_TRUNC('month', MIN(signup_date) OVER (PARTITION BY user_id)) (learn more in our SQL window functions guide)

Business days between two dates: varies by dialect; often requires a calendar table.

Time since last activity: CURRENT_DATE - MAX(activity_date) OVER (PARTITION BY user_id)

Practicing these on real data is how the syntax becomes second nature. Every dashboard you build will lean on at least three of them.

Final Thoughts

Dates are unglamorous but unavoidable — they underlie almost every business metric you will ever report on. The syntax varies enough between dialects that you should keep a cheat sheet handy for whichever database you are working in. Master DATE_TRUNC for period reporting, DATEDIFF or its equivalent for durations, interval arithmetic for relative dates, and always be deliberate about time zones. Once these patterns are in your muscle memory, the entire class of "what happened between these two dates" analytical questions becomes easy — which is exactly the fluency any data analytics course in Bangalore should be building toward.