Date Functions in SQL | Full Guide with Examples

What Are Date Functions in SQL?

Date functions in SQL are used to work with date and time values. These functions help you:

  • Get the current date and time
  • Extract parts like year, month, or day
  • Add or subtract time intervals
  • Format date values
  • Calculate differences between two dates

They are essential for data analysis, reporting, and time-based filtering in SQL queries.




Common SQL Date Functions


1. CURRENT_DATE / GETDATE()

Returns the current system date.


SELECT CURRENT_DATE; -- MySQL, PostgreSQL
SELECT GETDATE();     -- SQL Server


2. NOW()

Returns the current date and time (timestamp).


SELECT NOW(); -- MySQL, PostgreSQL


3. EXTRACT()

Extracts specific parts of a date, like year, month, or day.


SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS year;
SELECT EXTRACT(MONTH FROM CURRENT_DATE) AS month;


4. DATE_PART() (PostgreSQL)

Similar to EXTRACT() but used in PostgreSQL.


SELECT DATE_PART('day', CURRENT_DATE) AS day;


5. YEAR(), MONTH(), DAY()

Simple functions to get year, month, and day from a date.


SELECT YEAR('2025-04-29');
SELECT MONTH('2025-04-29');
SELECT DAY('2025-04-29');


6. DATEDIFF()

Calculates the difference in days between two dates.


SELECT DATEDIFF('2025-05-10', '2025-05-01'); -- MySQL
SELECT DATEDIFF(day, '2025-05-01', '2025-05-10'); -- SQL Server


7. DATE_ADD() / DATE_SUB()

Add or subtract time intervals from a date.


SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);  -- Add 7 days
SELECT DATE_SUB(CURDATE(), INTERVAL 1 MONTH); -- Subtract 1 month


8. TIMESTAMPDIFF() (MySQL)

Returns difference between two dates in various units (DAY, MONTH, YEAR).


SELECT TIMESTAMPDIFF(DAY, '2025-01-01', '2025-04-29');


9. DATE_FORMAT() (MySQL) / TO_CHAR() (PostgreSQL)

Formats a date into a custom string format.


SELECT DATE_FORMAT(CURDATE(), '%d-%m-%Y');  -- MySQL
SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY'); -- PostgreSQL


10. STR_TO_DATE() / TO_DATE()

Converts a string into a date.


SELECT STR_TO_DATE('29-04-2025', '%d-%m-%Y');  -- MySQL
SELECT TO_DATE('29-04-2025', 'DD-MM-YYYY');   -- PostgreSQL


Conclusion

Understanding SQL date functions is crucial for working with time-sensitive data, filtering records, generating reports, and scheduling events.