Current Date/Time Functions
- There are a number of functions that give the current date and time. The DATE() function is a date formatting function, but I include it in the list because it is often confused with the NOW() function
- CURRENT_DATE, CURRENT_TIME, UTC_DATE, UTC_TIME can be used with the parentheses “()” or not. They accept no parameters
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:
DATE_ADD
• 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:
DATE_FORMAT
• 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
|
DATEDIFF
- The DATEDIFF function has two parameters. Both are dates.
- The value returned by the function is an integer and is the number of days between the two dates.
- If you provide the latest date, first the results will be positive. Otherwise, it will be negative.
Example:
SELECT DATEDIFF('2018-01-01', '2019-01-01')
AS 'Date Difference';
Results:
