CodeCapsule

SQL Working With Dates

by

Here is a clear, practical, and complete guide to working with dates in SQL, including the most important functions and examples for MySQL, PostgreSQL, SQL Server, and Oracle.

✅ Date Types in SQL

Most databases use several date/time data types:

DB Engine Date Types
MySQL DATE, TIME, DATETIME, TIMESTAMP
PostgreSQL DATE, TIME, TIMESTAMP, TIMESTAMPTZ, INTERVAL
SQL Server DATE, TIME, DATETIME, DATETIME2
Oracle DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE

🔹 1. Getting the Current Date / Time

MySQL

SELECT NOW(); -- datetime
SELECT CURDATE(); -- date

PostgreSQL

SELECT NOW();
SELECT CURRENT_DATE;

SQL Server

SELECT GETDATE();
SELECT CURRENT_TIMESTAMP;

Oracle

SELECT SYSDATE FROM dual;

🔹 2. Extracting Parts of a Date

Universal (ANSI SQL)

SELECT EXTRACT(YEAR FROM order_date) AS year
FROM orders;

MySQL

SELECT YEAR(order_date), MONTH(order_date), DAY(order_date)
FROM orders;

PostgreSQL

SELECT DATE_PART('year', order_date);

SQL Server

SELECT YEAR(order_date), MONTH(order_date), DAY(order_date);

🔹 3. Formatting Dates

MySQL

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

PostgreSQL

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date
FROM orders;

SQL Server

SELECT FORMAT(order_date, 'yyyy-MM-dd') AS formatted_date
FROM orders;

Oracle

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders;

🔹 4. Date Arithmetic

Adding days, months, years

MySQL

SELECT DATE_ADD(order_date, INTERVAL 7 DAY);
SELECT DATE_SUB(order_date, INTERVAL 1 MONTH);

PostgreSQL

SELECT order_date + INTERVAL '7 days';
SELECT order_date - INTERVAL '1 month';

SQL Server

SELECT DATEADD(day, 7, order_date);
SELECT DATEADD(month, -1, order_date);

Oracle

SELECT order_date + 7 FROM orders; -- 7 days

🔹 5. Calculating Date Differences

MySQL

SELECT DATEDIFF(end_date, start_date) AS days_diff;

PostgreSQL

SELECT end_date - start_date AS days_diff;

SQL Server

SELECT DATEDIFF(day, start_date, end_date);

Oracle

SELECT end_date - start_date AS days_diff FROM orders;

🔹 6. Filtering by Date

SELECT *
FROM orders
WHERE order_date >= '2024-01-01';

Ranges

WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'

✔ Always use ISO format: YYYY-MM-DD

🔹 7. Working With Time Zones

PostgreSQL (best TZ support)

SELECT NOW() AT TIME ZONE 'UTC';
SELECT NOW() AT TIME ZONE 'America/New_York';

MySQL

SET time_zone = '+00:00';

SQL Server

SELECT SYSDATETIMEOFFSET();

🔹 8. Converting Strings to Dates

MySQL

SELECT STR_TO_DATE('2025-01-01', '%Y-%m-%d');

PostgreSQL

SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD');

SQL Server

SELECT CONVERT(DATE, '2025-01-01');

Oracle

SELECT TO_DATE('2025-01-01', 'YYYY-MM-DD') FROM dual;

🔥 Real-World Examples

Get orders placed today

WHERE DATE(order_date) = CURRENT_DATE

Get last 7 days of orders

MySQL/PostgreSQL:

WHERE order_date >= NOW() - INTERVAL '7 days'

SQL Server:

WHERE order_date >= DATEADD(day, -7, GETDATE())

Group sales by month

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

(MySQL uses DATE_FORMAT and SQL Server uses FORMAT or EOMONTH.)

🏎 Best Practices for Working With Dates

✔ Always store dates in UTC
✔ Convert to local time in the application layer
✔ Use TIMESTAMP/TIMESTAMPTZ instead of DATETIME where possible
✔ Avoid storing dates as strings
✔ Use ISO format (YYYY-MM-DD) for filters
✔ Index date columns for faster range queries
✔ Beware of timezone shifts (DST transitions)

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *