Blog

NOW(), DATE(), DATEADD(), DATEDIFF()

SQL database management systems have several built-in date and time functions that help database users insert, manage, control, compare, and customize or format dates and times in programs and databases. Here, some commonly used basic date and time functions are explained in detail, along with detailed examples. These include functions such as NOW(), DATE(), DATEADD(), and DATEDIFF(), etc.

NOW() SQL Function.

The NOW() function in SQL database management software is used to display the current date and time, including seconds, in the database software console window.

Syntax of the NOW() function.

NOW()

Elements of the NOW() SQL function.

• The NOW() function displays the current date and time information in YYYY-MM-DD HH:MM:SS format on the user console screen. The date and time format output in the NOW function depends on your database system software.

Example of the NOW() function.

To display the current date and time in your database software system:

SELECT NOW() AS current_datetime;

Result of the NOW() SQL function.

current_datetime

01/02/2026 05:33:21

In this example.

• In this example, the NOW() function displays the current date and time information on the console screen when the user query is run.

DATE() SQL Function.

The DATE() function in SQL database management software is used to extract date information from a datetime or timestamp value on the console screen. The DATE function extracts the time portion of the DATETIME value and displays only the current date information in YYYY-MM-DD format in the database software console screen.

Syntax of the DATE() function.

DATE(datetime_value)

Elements of the DATE() SQL Function.

• datetime_value – This is the value in the DATE function from which the user wants to extract the current date.

Example of the DATE() function.

To get only the date (without the time) from the current datetime column in the existing console screen software.

SELECT DATE(NOW()) AS current_date;

Result of the DATE() SQL Function.

current_date

2026-01-02

In this example.

• In this example, the DATE() function extracts only the current date portion from the result of the NOW() function and displays the date, removing the current time from the NOW function.

DATEADD() SQL Function.

The DATEADD() function in SQL database management software is used to add or subtract a specified time from a given date. Database users can modify and display the date and time interval information, such as year, month, day, hour, etc.

Syntax of the DATEADD() function.

DATEADD(interval, number, date)

Elements of the DATEADD() SQL Function.

• interval – This is the time unit to be added to the date and time. Elements include YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
• number – This is the number of units to be added to the date. Use a negative number to subtract.
• date – This is the starting date to which the interval will be added.

Example of the DATEADD() SQL Function.

To add 4 days to the current date.

SELECT DATEADD(DAY, 4, NOW()) AS new_date;

Result of the DATEADD() SQL Function.

new_date

2026-01-06 11:16:55

In this example.

• In this example, the DATEADD() function adds 4 days to the current date and time and displays that date and time information. Example of subtracting months from a date

To subtract 1 month from the current date and time.

SELECT DATEADD(MONTH, -1, NOW()) AS new_date;

Result of subtracting months from a date.

new_date

2025-12-02 14:32:33

In this example.

• the DATEADD() function subtracts 1 month from the current date and time and displays the resulting date and time information.

DATEDIFF() SQL Function.

The DATEDIFF() function in SQL database management software is used to extract the difference between two different dates. It returns the difference between the two dates as an integer value. The result of the DATEDIFF() function represents the difference between the two dates in user-defined units.

Syntax of the DATEDIFF() Function.

DATEDIFF(date1, date2)

Elements of the DATEDIFF() Function.

• date1 and date2 – Here, the result is calculated as the difference between two dates, date1 and date2, for comparison with the current date.
• The date result is the difference between the dates. Unless otherwise indicated in other SQL implementations, such as SQL Server, it can return the difference in years, months, etc., based on the units.

Example of the DATEDIFF() Function.

For example, to find the difference between the current date and a particular date, such as ‘2025-01-02’.

SELECT DATEDIFF(NOW(), ‘2025-01-02’) AS days_difference;

Result of the DATEDIFF() SQL Function.

days_difference

365

In this example.

• Here, in this example, the DATEDIFF() function returns the difference between the current date (NOW()) and ‘2025-01-02’.

Example of the difference in days between two dates.

SELECT DATEDIFF(‘2026-01-02’, ‘2025-11-02’) AS date_difference;

Result of the difference in days between two dates.

date_difference

61

In this example. In this example, the DATEDIFF() function calculates the difference between the dates ‘2026-01-02’ and ‘2025-11-02’, and returns the difference as 61 days.

Explanation of Functions

Summary of NOW(), DATE(), DATEADD(), and DATEDIFF() functions.

• The NOW() SQL database function displays the current date and time information.
• The DATE() SQL database function helps the user extract only the date information from a DATETIME value.
• The DATEADD() SQL database function is used to add or subtract a specified time interval (days, months, years, etc.) to a date.
• The DATEDIFF() SQL database function calculates the difference between two dates and displays the difference in days or other units depending on the system.