How to Work with Dates: MySQL SQL Query Examples

MySQL supports various Date operations that are essential in data analysis. Understanding these operations is crucial for your projects and interviews. Let’s explore some common date operations in MySQL.

ON THIS PAGE

1. MySQL Date Examples
   1. #1 Current Date using the now()
   2. #2 Get the current Date from dual
   3. #3 Get Sysdate from the dual
   4. #4 Get time diff from the different Timezones
   5. #5 Query to select Day and Month

MySQL Date Examples

• Getting current Date using Now()
• Get current_date from dual
• Get system Date from dual
• Get time diff from the different timezones
• Selecting Day and Month
  • MONTHS_BETWEEN
  • ADD_MONTHS
  • NEXT_DAY
  • LAST_DAY
  • DAY
  • DAYOFMONTH

#1 Current Date using the now()

SELECT NOW() FROM DUAL;

Output
2023-05-01 12:09:08

#2 Get the current Date from dual

SELECT CURRENT_DATE() FROM DUAL;

output
2023-05-01

#3 Get Sysdate from the dual

SELECT sysdate() from dual;

Output
2023-05-01 12:18:18

#4 Get time diff from the different Timezones

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

Output
05:30:00

#5 Query to select Day and Month

SELECT DAY(last_update) from actor;

Output
15

SELECT dayofmonth(last_update) from actor;

Output
15

SELECT *
FROM ACTOR
WHERE date(last_update) > '2021-08-01'
ORDER BY last_update;

Output

first_name      last_name  last_update
PENELOPE	GUINESS	   2006-02-15 04:34:33

SELECT date_format(now(),'%Y-%m');
Output
2023-05

SELECT date_format(now(),'%Y-%m-%d');
Output
2023-05-01

SELECT date_format(now(),'%Y-%m-%d %H');
Output
2023-05-01 17

SELECT date_format(now(),'%Y-%m-%d %H:%i');
Output
2023-05-01 17:31

Related