SQL Date and Time Functions

Handling date and time data in SQL is essential for many database operations. SQL provides a variety of date and time functions that help users work with date values, perform calculations, and format them as needed. Whether you’re adding intervals to dates, extracting parts of a date, or formatting the output, mastering SQL date functions is crucial for working with temporal data in databases.

In this article, we’ll explore the most common SQL date functions, including examples and use cases for each. We’ll cover functions like NOW(), CURDATE(), DATE_ADD(), DATE_SUB(), and many more, providing a clear understanding of how to use them effectively.

Common SQL Date Data Types

In SQL, dates are complicated for newbies, since while working with a database, the format of the data in the table must be matched with the input data to insert. In various scenarios instead of date, datetime (time is also involved with date) is used.

For storing a date or a date and time value in a database,SQL offers the following data types:

SQL Date Functions

1. NOW()

The NOW() function retrieves the current date and time in YYYY-MM-DD HH:MI:SS format.

Query:

SELECT NOW();

Output:

2. CURDATE()

CURDATE() returns the current date in YYYY-MM-DD format, without the time part.

Query:

SELECT CURDATE();

Output: 

3. CURTIME()

The CURTIME() function returns the current time in HH:MI:SS format, excluding the date.

Query:

SELECT CURTIME();

Output: 

4. DATE()

Extracts the date part of a date or date/time expression. Example: For the below table named ‘Test’

Query:

SELECT Name, DATE(BirthTime) 
AS BirthDate FROM Test;

Output:

5. EXTRACT()

The EXTRACT() function retrieves a specific part of a DATE, DATETIME, or TIMESTAMP value, such as the day, year, or second.

Syntax:

EXTRACT(unit FROM date);

Several units can be considered but only some are used such as MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc. And ‘date’ is a valid date expression. Example: For the below table named ‘Test’

Query:

SELECT Name, Extract(DAY FROM 
BirthTime) AS BirthDay FROM Test;

Output: 

Query:

SELECT Name, Extract(YEAR FROM BirthTime)
AS BirthYear FROM Test;

Output: 

Query:

SELECT Name, Extract(SECOND FROM 
BirthTime) AS BirthSecond FROM Test;

Output:

6. DATE_ADD() 

The DATE_ADD() function allows you to add a specified time interval to a date.

Syntax:

DATE_ADD(date, INTERVAL expr type);

Where,  date – valid date expression, and expr is the number of intervals we want to add. and type can be one of the following: MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc. Example: For the below table named ‘Test’

Query:

SELECT Name, DATE_ADD(BirthTime, INTERVAL 
1 YEAR) AS BirthTimeModified FROM Test;

Output:

Query:

SELECT Name, DATE_ADD(BirthTime, 
INTERVAL 30 DAY) AS BirthDayModified FROM Test;

Output:

Query:

SELECT Name, DATE_ADD(BirthTime, INTERVAL
 4 HOUR) AS BirthHourModified FROM Test;

Output: 

7. DATEDIFF()

 Returns the number of days between two dates. 

Syntax:

DATEDIFF(interval,date1, date2);

interval – minute/hour/month/year,etc

date1 & date2- date/time expression

Query:

SELECT DATEDIFF(day, '2017-01-13', '2017-01-03') AS DateDiff;

Output: 

8. DATE_FORMAT()

DATE_FORMAT() allows you to display DATE, DATETIME, or TIMESTAMP data in various formats, using placeholders.

Syntax:

DATE_FORMAT(date,format);

the date is a valid date and the format specifies the output format for the date/time. The formats that can be used are:

• %a-Abbreviated weekday name (Sun-Sat)
• %b-Abbreviated month name (Jan-Dec)
• %c-Month, numeric (0-12)
• %D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
• %d-Day of the month, numeric (00-31)
• %e-Day of the month, numeric (0-31)
• %f-Microseconds (000000-999999)
• %H-Hour (00-23)
• %h-Hour (01-12)
• %I-Hour (01-12)
• %i-Minutes, numeric (00-59)
• %j-Day of the year (001-366)
• %k-Hour (0-23)
• %l-Hour (1-12)
• %M-Month name (January-December)
• %m-Month, numeric (00-12)
• %p-AM or PM
• %r-Time, 12-hour (hh:mm: ss followed by AM or PM)
• %S-Seconds (00-59)
• %s-Seconds (00-59)
• %T-Time, 24-hour (hh:mm: ss)
• %U-Week (00-53) where Sunday is the first day of the week
• %u-Week (00-53) where Monday is the first day of the week
• %V-Week (01-53) where Sunday is the first day of the week, used with %X
• %v-Week (01-53) where Monday is the first day of the week, used with %x
• %W-Weekday name (Sunday-Saturday)
• %w-Day of the week (0=Sunday, 6=Saturday)
• %X-Year for the week where Sunday is the first day of the week, four digits, used with %V
• %x-Year for the week where Monday is the first day of the week, four digits, used with %v
• %Y-Year, numeric, four digits
• %y-Year, numeric, two digits

Conclusion

SQL provides a wide range of date and time functions that can simplify your work when dealing with temporal data. Whether you’re extracting specific parts of a date, adding or subtracting time intervals, or formatting date-time values, mastering these functions can significantly enhance your ability to query and manipulate date-related data effectively. By understanding and using functions like NOW(), CURDATE(), DATE_ADD(), DATEDIFF(), and DATE_FORMAT(), you can perform complex date operations with ease, making your SQL queries more efficient and powerful.

P

Pratik Agarwal

Improve

Article Tags :

• Databases
• SQL