SQL Date & Time Functions

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

SQL provides powerful functions to handle dates and times — essential for filtering, formatting, and calculating time-based data.

🔹 Common Date Functions (DBMS-Agnostic Examples)

-- Get current date and time
SELECT CURRENT_DATE;        -- PostgreSQL / MySQL
SELECT GETDATE();           -- SQL Server

-- Extract parts of a date
SELECT EXTRACT(YEAR FROM order_date) FROM orders;      -- PostgreSQL
SELECT YEAR(order_date) FROM orders;                   -- MySQL / SQL Server

-- Add or subtract dates
SELECT order_date + INTERVAL '7 day' FROM orders;      -- PostgreSQL
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders;  -- MySQL
SELECT DATEADD(DAY, 7, order_date) FROM orders;         -- SQL Server

-- Difference between dates
SELECT DATEDIFF(CURDATE(), order_date);     -- MySQL
SELECT DATEDIFF(DAY, order_date, GETDATE());-- SQL Server

-- Format dates
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders; -- PostgreSQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') FROM orders; -- MySQL
SELECT FORMAT(order_date, 'yyyy-MM-dd') FROM orders;  -- SQL Server

🔹 Examples

-- Find orders placed in last 30 days
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 day';  -- PostgreSQL

-- Find orders placed in a specific month
SELECT * FROM orders
WHERE MONTH(order_date) = 5;  -- May

🔹 Key Date Functions by DBMS

🧠 Quick Recap

💡 Use the right function depending on your DBMS, and always format or extract dates smartly for reports and filters.