Date and Time Functions - NOW, DATEADD, DATEDIFF, DATE_FORMAT in SQL

SQL provides several functions for working with date and time values. These functions allow you to retrieve the current date and time, add or subtract time intervals, calculate the difference between dates, and format dates and times in specific ways. In this article, we will explore some of the most commonly used date and time functions in SQL: NOW, DATEADD, DATEDIFF, and DATE_FORMAT.

1. NOW - Get the Current Date and Time

The NOW function is used to retrieve the current date and time from the system. This function is often used to track the current timestamp in database records or to compare against other date and time values.

Syntax:

      SELECT NOW();
    

NOW returns the current date and time in the format YYYY-MM-DD HH:MM:SS.

Example: Using NOW

      -- Get the current date and time
      SELECT NOW() AS CurrentDateTime;
    

This query returns the current date and time from the database server.

2. DATEADD - Add or Subtract Date/Time Intervals

The DATEADD function is used to add or subtract a specific time interval to a date. You can add or subtract days, months, years, hours, minutes, or seconds from a given date.

Syntax:

      SELECT DATEADD(interval, number, date);
    

DATEADD takes three arguments: the interval (such as DAY, MONTH, YEAR, etc.), the number of intervals to add (positive for addition, negative for subtraction), and the starting date.

Example: Using DATEADD

      -- Add 5 days to the current date
      SELECT DATEADD(DAY, 5, NOW()) AS NewDate;
    

This query adds 5 days to the current date and time.

Example: Subtracting Days with DATEADD

      -- Subtract 3 months from the current date
      SELECT DATEADD(MONTH, -3, NOW()) AS NewDate;
    

This query subtracts 3 months from the current date and time.

3. DATEDIFF - Calculate the Difference Between Two Dates

The DATEDIFF function is used to calculate the difference between two dates. The result is returned as an integer value representing the number of units (such as days, months, or years) between the two dates.

Syntax:

      SELECT DATEDIFF(date1, date2);
    

DATEDIFF calculates the difference between date1 and date2 and returns the result in terms of days.

Example: Using DATEDIFF

      -- Calculate the difference in days between two dates
      SELECT DATEDIFF('2024-12-31', '2024-01-01') AS DateDifference;
    

This query calculates the number of days between December 31, 2024, and January 1, 2024. The result is 364 days.

Example: Using DATEDIFF with Table Data

      -- Calculate the number of days an employee has been with the company
      SELECT EmployeeID, DATEDIFF(CURDATE(), HireDate) AS DaysWithCompany
      FROM Employees;
    

This query calculates how many days each employee has been with the company, based on their HireDate and the current date.

4. DATE_FORMAT - Format Date and Time in SQL

The DATE_FORMAT function is used to format date and time values in a specified format. This is useful when you need to display dates and times in a particular way, such as displaying only the month and year or formatting the time as a 12-hour clock.

Syntax:

      SELECT DATE_FORMAT(date, format);
    

DATE_FORMAT takes two arguments: the date value to format and the format string that defines how the date should be displayed. Common format specifiers include:

• %Y - Year (4 digits)
• %m - Month (2 digits)
• %d - Day of the month (2 digits)
• %H - Hour (00 to 23)
• %i - Minutes (00 to 59)
• %s - Seconds (00 to 59)

Example: Using DATE_FORMAT

      -- Format the current date as YYYY-MM-DD
      SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS FormattedDate;
    

This query formats the current date in the YYYY-MM-DD format, such as 2024-11-19.

Example: Using DATE_FORMAT with Time

      -- Format the current time as HH:MM:SS
      SELECT DATE_FORMAT(NOW(), '%H:%i:%s') AS CurrentTime;
    

This query formats the current time in the HH:MM:SS format, such as 14:30:15.

5. Conclusion

SQL date and time functions such as NOW, DATEADD, DATEDIFF, and DATE_FORMAT are essential for working with date and time data in your database. These functions allow you to retrieve the current timestamp, manipulate and calculate date values, and format them for display. Understanding how to use these functions will enhance your ability to handle date and time data effectively in SQL queries.