Date Functions in SQL
? Date Functions in SQL
Date functions help you work with dates and times — extracting parts, calculating differences, formatting, etc. Exact functions vary by SQL dialect, but here are the common ones found in most databases (MySQL, SQL Server, PostgreSQL, Oracle).
? Common Date Functions
| Function | Description | Example | Output Example |
|---|---|---|---|
NOW() / CURRENT_TIMESTAMP | Returns the current date and time | SELECT NOW(); | 2025-06-06 11:30:45 |
CURDATE() / CURRENT_DATE | Returns the current date (no time) | SELECT CURDATE(); | 2025-06-06 |
DATE() | Extract date part from datetime | SELECT DATE('2025-06-06 11:30'); | 2025-06-06 |
YEAR() | Extract year from date | SELECT YEAR('2025-06-06'); | 2025 |
MONTH() | Extract month number (1-12) | SELECT MONTH('2025-06-06'); | 6 |
DAY() / DAYOFMONTH() | Extract day of month | SELECT DAY('2025-06-06'); | 6 |
HOUR() | Extract hour from time | SELECT HOUR('11:30:45'); | 11 |
MINUTE() | Extract minute from time | SELECT MINUTE('11:30:45'); | 30 |
SECOND() | Extract second from time | SELECT SECOND('11:30:45'); | 45 |
DATEDIFF(date1, date2) | Difference in days between two dates | SELECT DATEDIFF('2025-06-06', '2025-06-01'); | 5 |
DATEADD() / ADDDATE() | Add interval to a date (days, months, etc.) | SELECT DATEADD(day, 7, '2025-06-01'); (SQL Server)SELECT ADDDATE('2025-06-01', INTERVAL 7 DAY); (MySQL) | 2025-06-08 |
FORMAT() | Format date/time into string | SELECT FORMAT(NOW(), 'yyyy-MM-dd'); (SQL Server) | 2025-06-06 |
? Example Queries
Get current date and time:
SELECT NOW();Find age in days between two dates:
SELECT DATEDIFF('2025-06-06', '2025-01-01') AS days_diff;Add 10 days to a date:
-- MySQLSELECT DATE_ADD('2025-06-01', INTERVAL 10 DAY);-- SQL ServerSELECT DATEADD(day, 10, '2025-06-01');Extract year, month, day:
SELECT YEAR('2025-06-06'), MONTH('2025-06-06'), DAY('2025-06-06');If you want, I can provide date function examples specific to your database (MySQL, PostgreSQL, SQL Server, Oracle), or explain advanced date/time operations!
