SQL Date and Time Functions

SQL date and time functions help you store, retrieve, and manipulate date and time values in a database. These functions are useful when you work with data like order dates, registration dates, login times, or event schedules.

You can use them to get the current date, calculate differences between dates, or extract specific parts like year or month.

Common SQL Date and Time Functions

Function	Description
NOW()	Returns current date and time
CURDATE()	Returns current date
CURTIME()	Returns current time
DATE()	Extracts date from datetime
YEAR()	Extracts year
MONTH()	Extracts month
DAY()	Extracts day
DATEDIFF()	Returns difference between two dates
DATE_ADD() / DATEADD()	Adds time to a date

1. NOW() Function

The NOW() function returns the current date and time.

SELECT NOW();

This is useful when inserting the current timestamp into a table.

2. CURDATE() Function

The CURDATE() function returns the current date only.

SELECT CURDATE();

Use it when you only need today’s date.

3. CURTIME() Function

The CURTIME() function returns the current time.

SELECT CURTIME();

It helps when you track login or activity time.

4. Extracting Year, Month, and Day

You can extract specific parts from a date.

SELECT YEAR(order_date) AS order_year
FROM orders;

SELECT MONTH(order_date) AS order_month
FROM orders;

SELECT DAY(order_date) AS order_day
FROM orders;

These functions help in reporting and filtering.

5. DATEDIFF() Function

The DATEDIFF() function calculates the difference between two dates.

SELECT DATEDIFF('2026-12-31', '2026-01-01') AS total_days;

It returns the number of days between the two dates.

You can also use it with table data.

SELECT DATEDIFF(CURDATE(), join_date) AS days_worked
FROM employees;

6. Adding Time to a Date

In MySQL, use DATE_ADD():

SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_date
FROM orders;

In SQL Server, use DATEADD():

SELECT DATEADD(DAY, 7, order_date) AS delivery_date
FROM orders;

These functions help calculate deadlines and future dates.

7. Using Date Functions with WHERE Clause

You can filter records using date functions.

SELECT *
FROM orders
WHERE YEAR(order_date) = 2026;

This returns all orders placed in 2026.

Important Notes

• Function names may vary slightly between MySQL, PostgreSQL, and SQL Server.
• Always store dates in proper date or datetime data types.
• Avoid storing dates as plain text.
• Use date functions for reporting, filtering, and calculations.

When to Use SQL Date and Time Functions

• Tracking orders and registrations
• Calculating age or duration
• Generating monthly or yearly reports
• Setting deadlines and reminders
• Filtering data based on time

Check out our resources!

• Bootstrap Templates: Explore our Bootstrap Projects section.
• Free E-Books: Download your Free E-Books here.