SoFunction
Updated on 2025-04-07

Detailed explanation of the examples of date functions commonly used when writing SQL queries

We are doing SQL query. The date function is a function we often use, which can be easily called for processing date and time data.

Here are some common date functions and their usage examples:

1. Report the date and time directly

  • CURRENT_DATE: Return to the current date.
  • NOW(): Returns the current date and time.

Example：

SELECT CURRENT_DATE; -- 2024-07-09
SELECT NOW(); -- 2024-07-09 10:00:00

2. Split the complete date and time into specific parts

  • YEAR(date): Extract the year from the date.
  • MONTH(date): Extract the month from the date.
  • DAY(date): Extract the date from the date.
  • HOUR(time): Extract hours from time.
  • MINUTE(time): Extract minutes from time.

Example：

SELECT YEAR('2024-07-09'); -- 2024
SELECT MONTH('2024-07-09'); -- 7
SELECT DAY('2024-07-09'); -- 9
SELECT HOUR('10:30:00'); -- 10
SELECT MINUTE('10:30:00'); -- 30

3. Calculate the interval between date and time

  • DATE_ADD(date, INTERVAL value unit): Add a time interval to the date.
  • DATE_SUB(date, INTERVAL value unit): Subtract one time interval from date.

Example：

  • SELECT DATE_ADD('2024-07-09', INTERVAL 1 DAY); -- 2024-07-10
SELECT DATE_SUB('2024-07-09', INTERVAL 1 MONTH); -- 2024-06-09

4. Format date and time

  • DATE_FORMAT(date, format): Return date in the specified format.

Example：

SELECT DATE_FORMAT('2024-07-09 10:30:00', '%Y-%m-%d %H:%i:%s'); -- 2024-07-09 10:30:00

5. Calculate the date difference between two dates

  • DATEDIFF(date1, date2): Calculate the difference in the number of days between two dates.

Example：

SELECT DATEDIFF('2024-07-10', '2024-07-09'); -- 1

6. Convert date format

  • STR_TO_DATE(string, format): Convert strings that match a specific format to date format.

Example：

SELECT STR_TO_DATE('2024-7-9', '%Y-%m-%d'); -- 2024-07-09

7. Week and Quarter

  • WEEKDAY(date): Returns the given date is the day of the week (0 = Monday, 1 = Tuesday,…, 6 = Sunday).
  • QUARTER(date): Return to the quarter where the date is.

Example：

SELECT WEEKDAY('2024-07-09'); -- Return the figure of the day of the week
SELECT QUARTER('2024-07-09'); -- 3

SQL time function is more convenient to use when we write queries, and you can get it now!

This is the article about the commonly used date functions when writing SQL queries. For more related SQL date functions, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!