Date and Time Functions

Dates show up everywhere — hire dates, order dates, signup dates. Every engine has its own date function names, so this article gives you a translation table alongside the concepts.

Getting the current date/time

SELECT CURRENT_DATE; -- PostgreSQL, MySQL, SQLite
SELECT GETDATE();     -- SQL Server
SELECT CURRENT_TIMESTAMP; -- works almost everywhere, includes time

Extracting parts of a date

-- PostgreSQL
SELECT EXTRACT(YEAR FROM hire_date), EXTRACT(MONTH FROM hire_date) FROM employees;

-- MySQL
SELECT YEAR(hire_date), MONTH(hire_date) FROM employees;

-- SQL Server
SELECT YEAR(hire_date), MONTH(hire_date) FROM employees;

-- SQLite (dates are stored as text, so use strftime)
SELECT strftime('%Y', hire_date), strftime('%m', hire_date) FROM employees;

Filtering by year

-- Employees hired in 2023 (works in PostgreSQL, MySQL, SQL Server)
SELECT * FROM employees WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';

Comparing dates as ISO-format strings ('YYYY-MM-DD') like this works reliably across every engine, including SQLite — it's often simpler than remembering each engine's YEAR()-style function.

Date arithmetic

-- PostgreSQL
SELECT hire_date + INTERVAL '90 days' FROM employees;

-- MySQL
SELECT DATE_ADD(hire_date, INTERVAL 90 DAY) FROM employees;

-- SQL Server
SELECT DATEADD(DAY, 90, hire_date) FROM employees;

-- SQLite
SELECT date(hire_date, '+90 days') FROM employees;

Difference between two dates

-- How many days each employee has been employed (PostgreSQL)
SELECT first_name, CURRENT_DATE - hire_date AS days_employed FROM employees;

-- MySQL / SQL Server
SELECT first_name, DATEDIFF(CURRENT_DATE, hire_date) AS days_employed FROM employees;

Formatting dates for display

Formatting functions vary the most between engines — TO_CHAR (PostgreSQL), DATE_FORMAT (MySQL), FORMAT (SQL Server), and strftime (SQLite). When learning, focus on the concepts (extracting parts, comparing ranges, computing differences) — you can always look up the exact formatting function for whichever engine you're deployed on.

Open the Code Playground, pick any SQL engine (SQLite, PostgreSQL, MySQL, or SQL Server), and paste these queries in directly — the practice database above is already loaded for you, every time.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;