-
Notifications
You must be signed in to change notification settings - Fork 0
MySQL
This is a function that adds a unit of time to an existing date. The format of the function is:
DATE_ADD(date, INTERVAL value, addunit)
Parameters for DATE_ADD() function:
date: starting date to which you want add time
value: the amount of time/days/month etc to add. This number can be positive or negative, depending on desired results. To get a time before the date supplied, value should be negative.
addunit: This is the unit of time to add. The date only options are: DAY, WEEK, MONTH, QUARTER, YEAR. The single unit of time only options are: MICROSECOND, SECOND, MINUTE, HOUR. The multiple units of time/date options (both units are changed by the same value supplied above): SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR, YEAR_MONTH.
If a time after the supplied data is needed, then the value should be positive. Note, for any units that use time, if you do not supply a time (see example below on how to add time), it will default to 12:00 am.
Examples of DATE_ADD
SELECT DATE_ADD("2020-01-01", INTERVAL 47 DAY); SELECT DATE_ADD("2019-12-01 09:34:21", INTERVAL -30 DAY_HOUR);
This is a MySQL function that formats a date per the user's specifications.
The format of DATE_FORMAT() is:
DATE_FORMAT(date, format)
Parameters of DATE_FORMAT():
date: This is the date to be formatted.
format: How the date should be formatted, can use one or many of the options.
Day options:
%a Abbreviated weekday name (Sun to Sat)
%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)
%j Day of the year (001 to 366)
%w Day of the week where Sunday=0 and Saturday=6
%W Weekday name in full (Sunday to Saturday)
Week options:
%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
Month options:
%b Abbreviated month name (Jan to Dec)
%c Numeric month name (0 to 12)
%M Month name in full (January to December)
%m Month name as a numeric value (00 to 12)
Year options:
%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
**Time options: **
%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)
%k Hour (0 to 23)
%l Hour (1 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)
Examples of DATE_FORMAT()
SELECT DATE_FORMAT("2020-03-15", "%W %M %e %Y"); --> returns: Sunday March 15 2020
SELECT DATE_FORMAT('2007-10-04 22:23:00', "%W %M %e %Y %l"); --> returns: Thursday October 4 2007 10
This function returns the name of the month.
Parameters for MONTHNAME() function:
MONTHNAME( date )
date: This is the date that will be processed and have the month returned.
Examples of MONTHNAME
SELECT MONTHNAME("2020-09-3"); --> returns September
This function returns the name of the day in a given date.
Parameters for DAYNAME() function:
DAYNAME( date )
date: This is the date that will be processed and have the day returned.
Examples of DAYNAME
SELECT DAYNAME("2020-09-3"); --> returns Thursday