Example
Return the year part of a date:
SELECT YEAR("2017-06-15");
Definition and Usage
The YEAR() function returns the year part for a given date (a number from 1000 to 9999).
Syntax
YEAR(date)
Tag: mysql command line
MySQL WEEKOFYEAR Function
Example
Return the week number for a date:
SELECT WEEKOFYEAR("2017-06-15");
Definition and Usage
The WEEKOFYEAR() function returns the week number for a given date (a number from 1 to 53).
Note: This function assumes that the first day of the week is Monday and the first week of the year has more than 3 days.
Syntax
WEEKOFYEAR(date)
MySQL WEEK Function
Example
Return the week number for a date:
SELECT WEEK("2017-06-15");
Definition and Usage
The WEEK() function returns the week number for a given date (a number from 0 to 53).
Syntax
WEEK(date, firstdayofweek)
MySQL TO_DAYS Function
Example
Return the number of days between the date and year 0:
SELECT TO_DAYS("2017-06-20");
Definition and Usage
The TO_DAYS() function returns the number of days between a date and year 0 (date “0000-00-00”).
The TO_DAYS() function can be used only with dates within the Gregorian calendar.
Syntax
TO_DAYS(date)
MySQL TIMESTAMP Function
Example
Return a datetime value based on the arguments:
SELECT TIMESTAMP("2017-07-23", "13:10:11");
Definition and Usage
The TIMESTAMP() function returns a datetime value based on a date or datetime value.
Note: If there are specified two arguments with this function, it first adds the second argument to the first, and then returns a datetime value.
Syntax
TIMESTAMP(expression, time)
MySQL TIMEDIFF Function
Example
Return the difference between two time expressions:
SELECT TIMEDIFF("13:10:11", "13:10:10");
Definition and Usage
The TIMEDIFF() function returns the difference between two time/datetime expressions.
Note: time1 and time2 should be in the same format, and the calculation is time1 – time2.
Syntax
TIMEDIFF(time1, time2)
MySQL TIME_TO_SEC Function
Example
Convert a time value into seconds:
SELECT TIME_TO_SEC("19:30:10");
Definition and Usage
The TIME_TO_SEC() function converts a time value into seconds.
Syntax
TIME_TO_SEC(time)
MySQL TIME_FORMAT Function
Example
Format a time:
SELECT TIME_FORMAT("19:30:10", "%H %i %s");
Definition and Usage
The TIME_FORMAT() function formats a time by a specified format.
Syntax
TIME_FORMAT(time, format)
MySQL TIME Function
Example
Extract the time part from a time expression:
SELECT TIME("19:30:10");
Definition and Usage
The TIME() function extracts the time part from a given time/datetime.
Note: This function returns “00:00:00” if expression is not a datetime/time, or NULL if expression is NULL.
Syntax
TIME(expression)
MySQL SYSDATE Function
Example
Return the current date and time:
SELECT SYSDATE();
Definition and Usage
The SYSDATE() function returns the current date and time.
Note: The date and time is returned as “YYYY-MM-DD HH:MM:SS” (string) or as YYYYMMDDHHMMSS (numeric).
Syntax
SYSDATE()