- Tags: MySQL
- Categories: Reference, SQL Reference, SQL Resources
Quick Reference
This is a quick reference list of MySQL date functions and how they are used to work with databases.
SQL – MySQL ADDDATE() Function
The MySQL ADDDATE() function adds a time/date interval to a date and then returns the date.
SQL – MySQL ADDTIME() Function
The MySQL ADDTIME() function adds a time interval to a time/datetime and then returns the time/datetime.
SQL – MySQL CURDATE() Function
The MySQL CURDATE() function returns the current date as “YYYY-MM-DD” (string) or as YYYYMMDD (numeric).
SQL – MySQL CURRENT_DATE() Function
The MySQL CURRENT_DATE() function returns the current date as “YYYY-MM-DD” (string) or as YYYYMMDD (numeric).
SQL – MySQL CURRENT_TIME() Function
The MySQL CURRENT_TIME() function returns the current time as “HH-MM-SS” (string) or as HHMMSS.uuuuuu (numeric).
SQL – MySQL CURRENT_TIMESTAMP() Function
The MySQL CURRENT_TIMESTAMP() function returns the current date and time as “YYYY-MM-DD HH-MM-SS” (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SQL – MySQL CURTIME() Function
The MySQL CURTIME() function returns the current time as “HH-MM-SS” (string) or as HHMMSS.uuuuuu (numeric).
SQL – MySQL DATE() Function
The MySQL DATE() function extracts the date part from a datetime expression.
SQL – MySQL DATEDIFF() Function
The MySQL DATEDIFF() function returns the number of days between two date values.
SQL – MySQL DATE_ADD() Function
The MySQL DATE_ADD() function adds a time/date interval to a date and then returns the date.
SQL – MySQL DATE_FORMAT() Function
The MySQL DATE_FORMAT() function formats a date as specified.
SQL – MySQL DATE_SUB() Function
The MySQL DATE_SUB() function subtracts a time/date interval from a date and then returns the date.
SQL – MySQL DAY() Function
The MySQL DAY() function returns the day of the month for a given date (a number from 1 to 31).
SQL – MySQL DAYNAME() Function
The MySQL DAYNAME() function returns the weekday name for a given date.
SQL – MySQL DAYOFMONTH() Function
The MySQL DAYOFMONTH() function returns the day of the month for a given date (a number from 1 to 31).
SQL – MySQL DAYOFWEEK() Function
The MySQL DAYOFWEEK() function returns the weekday index for a given date (a number from 1 – Sunday to 7 – Saturday).
SQL – MySQL DAYOFYEAR() Function
The MySQL DAYOFYEAR() function returns the day of the year for a given date (a number from 1 to 366).
SQL – MySQL EXTRACT() Function
The MySQL EXTRACT() function extracts a part from a given date.
SQL – MySQL FROM_DAYS() Function
The MySQL FROM_DAYS() function returns a date from a numeric datevalue.
SQL – MySQL HOUR() Function
The MySQL HOUR() function returns the hour part for a given date (from 0 to 838).
SQL – MySQL LAST_DAY() Function
The MySQL LAST_DAY() function extracts the last day of the month for a given date.
SQL – MySQL LOCALTIME() Function
The MySQL LOCALTIME() function returns the current date and time as “YYYY-MM-DD HH-MM-SS” (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SQL – MySQL LOCALTIMESTAMP() Function
The MySQL LOCALTIMESTAMP() function returns the current date and time as “YYYY-MM-DD HH-MM-SS” (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SQL – MySQL MAKEDATE() Function
The MySQL MAKEDATE() function creates and returns a date based on a year and a number of days value.
SQL – MySQL MAKETIME() Function
The MySQL MAKETIME() function creates and returns a time based on an hour, minute, and second value.
SQL – MySQL MICROSECOND() Function
The MySQL MICROSECOND() function returns the microsecond part of a time/datetime (from 0 to 999999).
SQL – MySQL MINUTE() Function
The MySQL MINUTE() function returns the minute part of a time/datetime (from 0 to 59).
SQL – MySQL MONTH() Function
The MySQL MONTH() function returns the month part for a given date (a number from 1 to 12).
SQL – MySQL MONTHNAME() Function
The MySQL MONTHNAME() function returns the name of the month for a given date.
SQL – MySQL NOW() Function
The MySQL NOW() function returns the current date and time as “YYYY-MM-DD HH:MM:SS” (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric).
SQL – MySQL PERIOD_ADD() Function
The MySQL PERIOD_ADD() function adds a specified number of months to a period and will return the result formatted as YYYYMM.
SQL – MySQL PERIOD_DIFF() Function
The MySQL PERIOD_DIFF() function returns the difference between two periods (both periods should be in the same format). The result will be in months.
SQL – MySQL QUARTER() Function
The MySQL QUARTER() function returns the quarter of the year for a given date value (a number from 1 to 4).
SQL – MySQL SECOND() Function
The MySQL SECOND() function returns the seconds part of a time/datetime (from 0 to 59).
SQL – MySQL SEC_TO_TIME() Function
The MySQL SEC_TO_TIME() function returns a time value (in format HH:MM:SS) based on the specified seconds (positive or negative).
SQL – MySQL STR_TO_DATE() Function
The MySQL STR_TO_DATE() function returns a date based on a string and a format.
SQL – MySQL SUBDATE() Function
The MySQL SUBDATE() function subtracts a time/date interval from a date and then returns the new date.
SQL – MySQL SUBTIME() Function
The MySQL SUBTIME() function subtracts time from a time/datetime expression and then returns the new time/datetime.
SQL – MySQL SYSDATE() Function
The MySQL SYSDATE() function returns the current date and time as “YYYY-MM-DD HH:MM:SS” (string) or as YYYYMMDDHHMMSS (numeric).
SQL – MySQL TIME() Function
The MySQL TIME() function extracts the time part from a given time/datetime, and returns “00:00:00” if not a datetime/time, or NULL if expression is NULL.
SQL – MySQL TIME_FORMAT() Function
The MySQL TIME_FORMAT() function formats a time by a specified format.
SQL – MySQL TIME_TO_SEC() Function
The MySQL TIME_TO_SEC() function converts a time value into seconds.
SQL – MySQL TIMEDIFF() Function
The MySQL TIMEDIFF() function returns the difference between two time/datetime expressions (written in the same format).
SQL – MySQL TIMESTAMP() Function
The MySQL TIMESTAMP() function returns a datetime value based on a date or datetime value.
SQL – MySQL TO_DAYS() Function
The MySQL TO_DAYS() function returns the number of days between a date and year 0 (date “0000-00-00”).
SQL – MySQL WEEK() Function
The MySQL WEEK() function returns the week number for a given date (a number from 0 to 53).
SQL – MySQL WEEKDAY() Function
The MySQL WEEKDAY() function returns the weekday number for a given date.
SQL – MySQL WEEKOFYEAR() Function
The MySQL WEEKOFYEAR() function returns the week number for a given date (a number from 1 to 53).
SQL – MySQL YEAR() Function
The MySQL YEAR() function returns the year part for a given date (a number from 1000 to 9999).
SQL – MySQL YEARWEEK() Function
The MySQL YEARWEEK() function returns the year and week number (a number from 0 to 53) for a given date.
We’d like to acknowledge that we learned a great deal of our coding from W3Schools and TutorialsPoint, borrowing heavily from their teaching process and excellent code examples. We highly recommend both sites to deepen your experience, and further your coding journey. We’re just hitting the basics here at 1SMARTchicken.
Why 1SMARTchicken?
See More →
Thanks for your support!
SQL Books
All are affiliate links. Thank you for your support!
SQL NOTES:
- Any work being done to modify the structure of a database or delete tables or the the database itself should only be done after making a recent backup
Feedback
If you see an error on the page or the code itself is incorrect or incomplete, or just plain wrong, please let us know. We’re always learning. NOTE: we do not sell your information and will not send you spam emails.
