SQL ADDDATE 函数

SQL ADDDATE() 是一个mySQL函数,用于给一个日期表达式加/减一个时间间隔。


注: 在MS SQL Server中,SQL ADDDATE()函数有相似的功能。

SQL ADDDATE 语法

ADDDATE(date-expression, INTERVAL expr type)

或者我们可以使用简化的语法来增加天数:

ADDDATE(date-expression, days)

type可以是下面中的一个:

type 值 格式 例子
YEAR_MONTH ‘YEARS-MONTHS’ INTERVAL ’2-3′ YEAR_MONTH
DAY_HOUR ‘DAYS HOURS’ INTERVAL ’2 12′ DAY_HOUR
DAY_MINUTE ‘DAYS HOURS:MINUTES’ INTERVAL ’1 2:20′ DAY_HOUR
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’ INTERVAL ’1 2:20:15′ DAY_SECOND
DAY_MICROSECOND ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ INTERVAL ’1 2:20:15.78′ DAY_MICROSECOND
HOUR_MINUTE ‘HOURS:MINUTES’ INTERVAL ’2:12′ HOUR_MINUTE
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’ INTERVAL ’2:12:4′ HOUR_SECOND
HOUR_MICROSECOND ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ INTERVAL ’2:12:4.566′ HOUR_MICROSECOND
MINUTE_SECOND ‘MINUTES:SECONDS’ INTERVAL ’12:24′ MINUTE_SECOND
MINUTE_MICROSECOND ‘MINUTES:SECONDS.MICROSECONDS’ INTERVAL
’12:24.756′ MINUTE_MICROSECOND
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’ INTERVAL ’24.756′ SECOND_MICROSECOND
YEAR YEARS INTERVAL 2 YEAR
QUARTER QUARTERS INTERVAL 2 QUARTER
MONTH MONTHS INTERVAL 2 MONTH
WEEK WEEKS INTERVAL 2 WEEK
DAY DAYS INTERVAL 2 DAY
HOUR HOURS INTERVAL 2 HOUR
MINUTE MINUTES INTERVAL 2 MINUTE
SECOND SECONDS INTERVAL 2 SECOND
MICROSECOND MICROSECONDS INTERVAL 2 MICROSECOND

我们也可以用更短的语法:

[日期表达式] + INTERVAL expr type
[日期表达式] - INTERVAL expr type

SQL ADDDATE 范例

给一个日期加3天:

SELECT ADDDATE('2011-09-11', INTERVAL 3 DAY) AS Add3Days

或:

SELECT ADDDATE('2011-09-11',3) AS Add3Days

或:

SELECT '2011-09-11' + INTERVAL 3 DAY AS Add3Days

它们会产生一个相同的结果:

Add3Days
2011-09-14

给一个日期加2小时10分钟:

SELECT ADDDATE('2011-09-11 2:42:36', INTERVAL '2:10' HOUR_MINUTE) AS NewDate

或:

SELECT '2011-09-11 2:42:36' + INTERVAL '2:10' HOUR_MINUTE AS NewDate

它们会产生一个相同的结果:

NewDate
2011-09-11 04:52:36

给一个日期减去2小时10分钟:

SELECT ADDDATE('2011-09-11 2:42:36', INTERVAL '-2:10' HOUR_MINUTE) AS NewDate

或:

SELECT'2011-09-11 2:42:36' - INTERVAL '2:10' HOUR_MINUTE AS NewDate

结果会类似于:

NewDate
2011-09-11 00:32:36