Day 22: Date Functions In SQL

Day 22 Of Learning

Image By Author

Hey guys, hope you’re good.

Today we’ll be looking into our date functions specifically in MySQL.

So, let’s get started.

What Are Date Functions?

Date functions are functions that apply to your dates in a dataset or a table. There are many date functions however we will be discussing the most common ones. Few examples are CURDATE, NOW, YEAR, DAY, MONTH, DATEDIFF, etc.

Common Date Functions In MySQL

Here are 8 most common date functions in MySQL. These depend on the context or the usecase, however I’m sure that these will be common in most use cases.

1. NOW() – Get current date & time (YYYY-MM-DD HH:MM:SS).

   SELECT NOW(); -- Returns 2025-02-27 14:30:45

2. CURDATE() – Get today’s date (YYYY-MM-DD).

   SELECT CURDATE(); -- Returns 2025-02-27

3. CURTIME() – Get current time (HH:MM:SS).

   SELECT CURTIME(); -- Returns 14:30:45

4. YEAR(), MONTH(), DAY() – Extract year, month, or day from a date.

   SELECT YEAR('2025-02-27'); -- Returns 2025

   SELECT MONTH('2025-02-27'); -- Returns 2

   SELECT DAY('2025-02-27'); -- Returns 27

5. DATE_ADD() / DATE_SUB() – Add or subtract days, months, or years.

   SELECT DATE_ADD('2025-02-27', INTERVAL 7 DAY); -- Returns 2025-03-06

   SELECT DATE_ADD('2025-02-27', INTERVAL 2 MONTH); -- Returns 2025-04-27

   SELECT DATE_SUB('2025-02-27', INTERVAL 10 DAY); -- Returns 2025-02-17

6. DATEDIFF() – Find the difference in days between two dates.

   SELECT DATEDIFF('2025-03-10', '2025-02-27'); -- Returns 11

7. STR_TO_DATE() – Convert a string into a date format.

   SELECT STR_TO_DATE('27-02-2025', '%d-%m-%Y'); -- Returns 2025-02-27

8. DATE_FORMAT() – Format a date in a custom way.

   SELECT DATE_FORMAT(NOW(), '%M %d, %Y'); -- Returns 'February 27, 2025'

Exercise

Here’s what you need to do:

1. Load any dataset

2. Use NOW(), YEAR(), MONTH(), DAY() in your table

3. What did you get? Comment below.

   ---

   I am a BI Engineer working at a Data And AI Firm. I’m trying to help newbies in Data land their first data job and impact as many fellow data professionals as I can. Here’s my contact info:

   1. ajmal7809@gmail.com

   2. Linkedin

   3. bideveloper_ (Discord)