-
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)