Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

DATE_ADD(date, INTERVAL value Type) and ADD_DATE(date,

INTERVAL value Type)

Adds the value to the date argument. The word INTERVAL is a keyword and must be used in the function.
The Type can be one of the types shown in Table B.1.
Table B.1 Time/Date Types

Type Expected Value

SECOND SECONDS

MINUTE MINUTES

HOUR HOURS

DAY DAYS

MONTH MONTHS

YEAR YEARS

MINUTE_SECOND "MINUTES:SECONDS"

HOUR_MINUTE "HOURS:MINUTES"

DAY_HOUR "DAYS HOURS"

YEAR_MONTH "YEARS-MONTHS"

HOUR_SECOND "HOURS:MINUTES:SECONDS"

DAY_MINUTE "DAYS HOURS:MINUTES"

DAY_SECOND "DAYS

HOURS:MINUTES:SECONDS"

If you use one of the types in the first column, the value must match the format in the second column.

See DATE_SUB().

Example:

SELECT DATE_ADD("2000-01-27", INTERVAL 4 DAY)

Result:

2000-01-31

Example:

SELECT DATE_ADD("2000-04-23 23:59:59", INTERVAL "1:1" MINUTE_SECOND)

Result:

2000-04-24 00:01:00

DATE_FORMAT(date, format_symbol)

Formats the given date according to the format given in the format_symbol. More than one symbol can be
used to describe a format. Table B.2 lists the acceptable format symbols.
Table B.2 Format Symbols

Format Symbol Meaning

%M (^) Full month
name
%m (^) Month—
numeric
%b (^) Abbreviated
month
name
%W (^) Full
weekday
name
%D
Day of the
month
%Y (^) Year—4
digit