MySQL Tutorial

Define MySQL

MySQL Create Database

MySQL Create Table

MySQL Constraints

MySQL Case Sensitivity

MySQL Insert

MySQL Select

MySQL Data Types

MySQL OrderBy

MySQL Top

MySQL Distinct

MySQL Update

MySQL Delete

MySQL Truncate

MySQL Drop

MySQL Alter

MySQL Join

MySQL Aggregate Function

MySQL String Function

MySQL Date Function

Page Stats

Visitor: 266

MySQL Date Functions

Date functions are used to manipulate or perform operation on date values. The following table list some useful date functions:

FunctionDescription
NOW()Returns the current date and time.
CURDATE()Returns the current date.
CURTIME()Returns the current time.
DATE()Extract the date part of a date or datetime expression.
DAY()Returns the day of the month (0-31).
DAYNAME()Returns the name of the weekday.
MONTH()Returns the month from the date passed (1-12).
MONTHNAME()Returns the name of the month.
YEAR()Returns the year.
DATE_FORMAT()Displays date and time value in other formats.
EXTRACT()Extract part of a date.
DATE_ADD()Adds a specified time value (or interval) to a date value.
DATE_SUB()Subtracts a specified time value (or interval) from a date value.
DATEDIFF()Returns the number of days between two dates.

Note: The date and time returned by these functions is the date and time of the computer on which the instance of database server is running.

%a	Abbreviated weekday name (Sun to Sat)
%b	Abbreviated month name (Jan to Dec)
%c	Numeric month name (0 to 12)
%D	Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d	Day of the month as a numeric value (01 to 31)
%e	Day of the month as a numeric value (0 to 31)
%f	Microseconds (000000 to 999999)
%H	Hour (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minutes (00 to 59)
%j	Day of the year (001 to 366)
%k	Hour (0 to 23)
%l	Hour (1 to 12)
%M	Month name in full (January to December)
%m	Month name as a numeric value (00 to 12)
%p	AM or PM
%r	Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)
%T	Time in 24 hour format (hh:mm:ss)
%U	Week where Sunday is the first day of the week (00 to 53)
%u	Week where Monday is the first day of the week (00 to 53)
%V	Week where Sunday is the first day of the week (01 to 53). Used with %X
%v	Week where Monday is the first day of the week (01 to 53). Used with %X
%W	Weekday name in full (Sunday to Saturday)
%w	Day of the week where Sunday=0 and Saturday=6
%X	Year for the week where Sunday is the first day of the week. Used with %V
%x	Year for the week where Monday is the first day of the week. Used with %V
%Y	Year as a numeric, 4-digit value
%y	Year as a numeric, 2-digit value

SQL Server Date Functions

The following table list some useful MySQL Server's built-in date functions:

FunctionDescription
GETDATE()Returns the current date and time.
DATEPART() Returns the specified datepart of the specified date e.g. DATEPART(year,'2016-10-25') return 2016.
DAY()Returns the day of the month (0-31).
MONTH()Returns the month from the specified date (0-12).
YEAR()Returns the year from the specified date.
DATEADD()Adds or subtracts a specified time interval from a date.
DATEDIFF()Returns the date or time between two specified dates.
CONVERT()Displays date and time value in other formats.