Table 1. Current Date Functions
Function | Type | Example | Result |
---|---|---|---|
NOW() * Returns current local date and time. |
date/time |
NOW() |
ex. ‘2020-02-24 09:31:31’ |
DATE(date) * extracts the date from input. If time is included, the time is dropped. |
date/time |
DATE('2020-01-01 11:31:31') |
‘2020-02-24’ |
CURRENT_DATE() * Returns current local date |
date |
CURRENT_DATE |
‘2020-02-24’ |
CURRENT_TIME() * Returns current local time. |
time |
CURRENT_TIME |
‘11:52:10’ |
UTC_DATE() * Returns current UTC date. |
date |
UTC_DATE |
‘2020-02-24’ |
UTC_TIME() * Returns current UTC date. |
time |
UTC_TIME |
‘18:52:10’ |
SELECT NOW() AS 'NOW()',
DATE('2020-01-01') AS 'DATE(), date only',
CURRENT_DATE AS 'CURRENT_DATE',
CURRENT_TIME AS 'CURRENT_TIME',
UTC_DATE AS 'UTC_DATE',
UTC_TIME AS 'UTC_TIME';
Results:
• Returns a date with a DATE or DATETIME value equal to the original value plus the specified interval.
Table 2. DATE_ADD Function
Function | Type | Example | Result |
---|---|---|---|
DATE_ADD(date, interval expression unit) |
DATE, DATETIME |
DATE_ADD(‘2020-01-01’, INTERVAL 1 DAY) |
‘202-01-02’ |
Code Snippet:
USE bike;
SELECT order_date,
DATE_ADD(order_date, INTERVAL 1 DAY) AS 'ORDER DATE PLUS 1 day',
DATE_ADD(order_date, INTERVAL 6 MONTH) AS 'ORDER DATE PLUS 6 months',
DATE_ADD(order_date, INTERVAL '2 12' DAY_HOUR)
AS 'ORDER DATE PLUS 2 days 1 hour'
FROM cust_order;
Results:
• Dates must be enclosed in quotes • You can pass a DATE or DATETIME datatype to DATE_FORMAT
Table 3. DATE_FORMAT Function
Function | Type | Example | Result |
---|---|---|---|
DATE_FORMAT |
DATE |
DATE_FORMAT(‘2020-09-03’, ‘%m/%d/%y’) |
09/03/14 |
Code Snippet:
USE world;
SELECT name, continent, DATE_FORMAT('2020-01-28', '%m/%d/%y')
FROM country;
Results:
Table 4. Format List
Specifier | Description |
---|---|
%a |
Abbreviated weekday name (Sun..Sat) |
%b |
Abbreviated month name (Jan..Dec) |
%c |
Month, numeric (0..12) |
%D |
Day of the month with English suffix (0th, 1st, 2nd, 3rd, …) |
%d |
Day of the month, numeric (00..31) |
%e |
Day of the month, numeric (0..31) |
%f |
Microseconds (000000..999999) |
%H |
Hour (00..23) |
%h |
Hour (01..12) |
%I |
Hour (01..12) |
%i |
Minutes, numeric (00..59) |
%j |
Day of year (001..366) |
%k |
Hour (0..23) |
%l |
Hour (1..12) |
%M |
Month name (January..December) |
%m |
Month, numeric (00..12) |
%p |
AM or PM |
%r |
Time, 12-hour (hh:mm:ss followed by AM or PM) |
%S |
Seconds (00..59) |
%s |
Seconds (00..59) |
%T |
Time, 24-hour (hh:mm:ss) |
%U |
Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 |
%u |
Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
%V |
Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X |
%v |
Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x |
%W |
Weekday name (Sunday..Saturday) |
%w |
Day of the week (0=Sunday..6=Saturday) |
%X |
Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
%x |
Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v |
%Y |
Year, numeric, four digits |
%y |
Year, numeric (two digits) |
%% |
A literal % character |
%x |
x, for any “x” not listed above |
Example:
SELECT DATEDIFF('2018-01-01', '2019-01-01')
AS 'Date Difference';
Results:
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/learning_mysql/date_functions.