Detailed explanation of single-row function code of date type in MySQL

Date-type single-row functions in MySQL:

CURDATE() or CURRENT_DATE() returns the current date (accurate to the day)
The reference code is as follows:

SELECT CURDATE()
FROM DUAL;

SELECT CURRENT_DATE()
FROM DUAL;

-----------------------------------(dividing line)------------------------------------

CURTIME() or CURRENT_TIME() returns the current time (accurate to seconds)
The reference code is as follows:

SELECT CURTIME()
FROM DUAL;

SELECT CURRENT_TIME()
FROM DUAL;

-----------------------------------(dividing line)------------------------------------

NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() returns the current system date and time (accurate to seconds)
The reference code is as follows:

SELECT NOW()
FROM DUAL;

SELECT LOCALTIME()
FROM DUAL;

-----------------------------------(dividing line)------------------------------------

YEAR(date) displays year / MONTH(date) displays month / DAY(date) displays day / HOUR(time) displays hour / MINUTE(TIME) displays minute / SECOND(TIME) displays second Reference code is as follows:

SELECT YEAR(CURDATE()), MONTH(CURDATE()), DAY(CURDATE())
FROM DUAL;

SELECT HOUR(CURTIME()),MINUTE(NOW()),SECOND(NOW())
FROM DUAL;

Note: The functions here, and the contents in the brackets come from the functions mentioned above.

-----------------------------------(dividing line)------------------------------------

WEEK(date) / WEEKOFYEAR(date) returns the first week of the year. The reference code is as follows:

SELECT WEEK(NOW())
FROM DUAL;

SELECT WEEKOFYEAR(CURDATE())
FROM DUAL;

-----------------------------------(dividing line)------------------------------------

DAYOFWEEK(DATE) returns the day of the week. Note: Sunday is 1, Monday is 2, ... Saturday is 7
The reference code is as follows:

SELECT DAYOFWEEK(NOW())  
FROM DUAL;

The code compilation results are as follows:

Explain that it is Thursday

-----------------------------------(dividing line)------------------------------------

WEEKDAY(DATE) returns the day of the week. Note that Monday is 0, Tuesday is 1, ... Sunday is 6
The reference code is as follows:

SELECT WEEKDAY(NOW())  
FROM DUAL;

The code compilation results are as follows:

Explain that it is Thursday

-----------------------------------(dividing line)------------------------------------

8. DAYNAME(date) returns the day of the week: MONDAY, TUESDAY…SUNDAY
The reference code is as follows:

SELECT DAYNAME(CURDATE()),DAYNAME('2020-09-06') #implicit conversion FROM DUAL;

The code compilation results are as follows:

Remark:
A conversion is used here, and the date can be converted to a string by default, which is called implicit conversion. In addition, a few common concepts are added:
Formatting: Converting a date to a string Parsing: Converting a string to a date Example code is as follows:

Convert string to date (parse) (explicit conversion)

SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;

The code compilation results are as follows:

Convert string to date (parse) (explicit conversion)

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;

The code compilation results are as follows:

Convert string to date (parse) (explicit conversion)

SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;

The code compilation results are as follows:

-----------------------------------(dividing line)------------------------------------

9. MONTHNAME(DATE) returns the month: January…

SELECT MONTHNAME(NOW())  
FROM DUAL;

-----------------------------------(dividing line)------------------------------------

DATEDIFF(date1,date2) returns the date interval between date1 and date2.
TIMEDIFF(time1,time2) returns the time interval of time1 - time2. The reference code is as follows:

SELECT DATEDIFF('2021-03-06','2021-06-09')  
FROM DUAL;

Note: The returned value is the number of days between the two dates = date1-date2. If date1 is before date2, the returned value is a negative value.
The code compilation results are as follows:

SELECT TIMEDIFF('2019-06-06 18:23:06','2019-08-06 10:36:45')
FROM DUAL;

Note: The returned value is the difference between the two times [hours: minutes: seconds] = time1-time2. If time1 is before time2, the value returned is a negative value.
The code compilation results are as follows:

The above is the details of the date type single-row function in MySQL. For more information about MySQL single-row functions, please pay attention to other related articles on 123WORDPRESS.COM!