MySQL Date Operations: The Helpful SQL Queries

Date operations in data analysis play a critical role. These MySQL Date operations SQL queries are helpful for your project and interviews. You will learn these Date Operations here.

ON THIS PAGE

1. MySQL Date Operations
   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 Operations

• 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