©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQLMariaDB

The SUBDATE SQL Function in MySQL and MariaDB - Subtract from date and time

SUBDATESyntax:SUBDATE(Date, [INTERVAL] Number [Unit])
Return value:DATE
Synonyms:DATE_SUB
Function type:Date and Time function
Function Description

The SUBDATE() function subtracts a "Number" of "Unit" from "Date".

The argument "Date" is of the type DATE or DATETIME.

The argument "Number" can also be negative to add to the "Date".


The "Unit" argument specifies the date unit to be subtracted from "Date":

UnitDescriptionSyntax
MICROSECOND MicrosecondsNumber
SECOND SecondsNumber
MINUTE MinutesNumber
HOUR HoursNumber
DAY DaysNumber
WEEK WeeksNumber
MONTH MonthsNumber
QUARTER Quarters (3 months)Number
YEAR YearsNumber
SECOND_MICROSECOND Seconds and microseconds'20:90'
MINUTE_MICROSECOND Minutes, seconds and microseconds'15:20:90'
MINUTE_SECOND Minutes and seconds'15:20'
HOUR_MICROSECOND Hours, minutes, seconds and microseconds'3:15:20:90'
HOUR_SECOND Hours, minutes and seconds'3:15:20'
HOUR_MINUTE Hours and minutes'3:15'
DAY_MICROSECOND Days, hours, minutes, seconds and microseconds'2:3:15:20:90'
DAY_SECOND Days, hours, minutes and seconds'2:3:15:20'
DAY_MINUTE Days, hours and minutes'3:15:5'
DAY_HOUR Days and hours'15:5'
YEAR_MONTH Years and months'3:4'


If "Number" is specified without INTERVAL and "Unit", "Number" of days are subtracted from "Date".

If the value of "Date" or "Number" is NULL, the ADDDATE() function returns NULL.

SQL Examples for the SUBDATE function

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

select now(), subdate(now(), interval 1 second);
select now(), subdate(now(), interval 1 minute);
select now(), subdate(now(), interval 1 hour);
select now(), subdate(now(), interval 1 day);
select now(), subdate(now(), interval 1 month);
select now(), subdate(now(), interval 1 year);

select now(), subdate(now(), interval -5 second);
select now(), subdate(now(), interval -5 minute);
select now(), subdate(now(), interval -5 hour);
select now(), subdate(now(), interval -5 day);
select now(), subdate(now(), interval -5 month);
select now(), subdate(now(), interval -5 year);

select now(), subdate(now(), interval '20:90' SECOND_MICROSECOND);
select now(), subdate(now(), interval '15:20:90' MINUTE_MICROSECOND);
select now(), subdate(now(), interval '15:20' MINUTE_SECOND);
select now(), subdate(now(), interval '3:15:20:90' HOUR_MICROSECOND);
select now(), subdate(now(), interval '3:15:20' HOUR_SECOND);
select now(), subdate(now(), interval '3:15' HOUR_MINUTE);
select now(), subdate(now(), interval '2:3:15:20:90' DAY_MICROSECOND);
select now(), subdate(now(), interval '2:3:15:20' DAY_SECOND);
select now(), subdate(now(), interval '2:3:15' DAY_MINUTE);
select now(), subdate(now(), interval '2:3' DAY_HOUR);
select now(), subdate(now(), interval '3:4' YEAR_MONTH);

select now(), subdate(now(), 5);

select subdate(nullinterval 5 year);
select subdate(now(), interval null year);

now()
subdate(now(), interval 1 second)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 17:12:34
now()
subdate(now(), interval 1 minute)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 17:11:35
now()
subdate(now(), interval 1 hour)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 16:12:35
now()
subdate(now(), interval 1 day)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-23 17:12:35
now()
subdate(now(), interval 1 month)
datetime(19)datetime(19)
2023-03-24 17:12:352023-02-24 17:12:35
now()
subdate(now(), interval 1 year)
datetime(19)datetime(19)
2023-03-24 17:12:352022-03-24 17:12:35
now()
subdate(now(), interval -5 second)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 17:12:40
now()
subdate(now(), interval -5 minute)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 17:17:35
now()
subdate(now(), interval -5 hour)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 22:12:35
now()
subdate(now(), interval -5 day)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-29 17:12:35
now()
subdate(now(), interval -5 month)
datetime(19)datetime(19)
2023-03-24 17:12:352023-08-24 17:12:35
now()
subdate(now(), interval -5 year)
datetime(19)datetime(19)
2023-03-24 17:12:352028-03-24 17:12:35
now()
subdate(now(), interval '20:90' SECOND_MICROSECOND)
datetime(19)datetime(26)
2023-03-24 17:12:352023-03-24 17:12:14.100000
now()
subdate(now(), interval '15:20:90' MINUTE_MICROSECOND)
datetime(19)datetime(26)
2023-03-24 17:12:352023-03-24 16:57:14.100000
now()
subdate(now(), interval '15:20' MINUTE_SECOND)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 16:57:15
now()
subdate(now(), interval '3:15:20:90' HOUR_MICROSECOND)
datetime(19)datetime(26)
2023-03-24 17:12:352023-03-24 13:57:14.100000
now()
subdate(now(), interval '3:15:20' HOUR_SECOND)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 13:57:15
now()
subdate(now(), interval '3:15' HOUR_MINUTE)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-24 13:57:35
now()
subdate(now(), interval '2:3:15:20:90' DAY_MICROSECOND)
datetime(19)datetime(26)
2023-03-24 17:12:352023-03-22 13:57:14.100000
now()
subdate(now(), interval '2:3:15:20' DAY_SECOND)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-22 13:57:15
now()
subdate(now(), interval '2:3:15' DAY_MINUTE)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-22 13:57:35
now()
subdate(now(), interval '2:3' DAY_HOUR)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-22 14:12:35
now()
subdate(now(), interval '3:4' YEAR_MONTH)
datetime(19)datetime(19)
2023-03-24 17:12:352019-11-24 17:12:35
now()
subdate(now(), 5)
datetime(19)datetime(19)
2023-03-24 17:12:352023-03-19 17:12:35
subdate(null, interval 5 year)
char(19) BINARY
NULL
subdate(now(), interval null year)
datetime(19)
NULL
The examples were created with the MyWAY SQL manager: Download

How to use the SUBDATE() function in MySQL and MariaDB databases

In MySQL and MariaDB the DATE_SUB() function is used to subtract a specific number days or other date units from a date. This allows date calculations to be performed and can be useful for tasks such as calculating future or past dates, generating date ranges, and performing date-based calculations in queries.
STDDEV_SAMPSTR_TO_DATESTRCMPSUBDATESUBSTRSUBSTRINGSUBSTRING_INDEX

Further MySQL und MariaDB SQL Date and Time functions

ADDDATEADDDATE(Date, [INTERVAL] Number [Unit])
More about ADDDATE Function

ADDTIMEADDTIME(Date, Expression)
More about ADDTIME Function

CONVERT_TZCONVERT_TZ(Date, From time zone, To time zone)
More about CONVERT_TZ Function

CURDATECURDATE()
More about CURDATE Function

CURTIMECURTIME([Precision])
More about CURTIME Function

CURRENT_DATECURRENT_DATE(-)
More about CURRENT_DATE Function

CURRENT_TIMECURRENT_TIME([Precision])
More about CURRENT_TIME Function

CURRENT_TIMESTAMPCURRENT_TIMESTAMP([Precision])
More about CURRENT_TIMESTAMP Function

DATEDATE(Date and Time)
More about DATE Function

DATE_ADDDATE_ADD(Date, INTERVAL Number Unit)
More about DATE_ADD Function

DATE_SUBDATE_SUB(Date, INTERVAL Number Unit)
More about DATE_SUB Function

FROM_DAYSFROM_DAYS(Number of days)
More about FROM_DAYS Function

FROM_UNIXTIMEFROM_UNIXTIME(Unix timestamp [, Format])
More about FROM_UNIXTIME Function

LOCALTIMELOCALTIME([Precision])
More about LOCALTIME Function

LOCALTIMESTAMPLOCALTIMESTAMP([Precision])
More about LOCALTIMESTAMP Function

MAKEDATEMAKEDATE(Year, Day of year)
More about MAKEDATE Function

MAKETIMEMAKETIME(Hours, Minutes, Seconds)
More about MAKETIME Function

NOWNOW([Precision])
More about NOW Function

SEC_TO_TIMESEC_TO_TIME(Seconds)
More about SEC_TO_TIME Function

SLEEPSLEEP(Seconds)
More about SLEEP Function

STR_TO_DATESTR_TO_DATE(String, Format)
More about STR_TO_DATE Function

SUBTIMESUBTIME(Date and Time, Deduction)
More about SUBTIME Function

SYSDATESYSDATE()
More about SYSDATE Function

TIMETIME(Date and Time)
More about TIME Function

TIMEDIFFTIMEDIFF(Date 1, Date 2)
More about TIMEDIFF Function

TIMESTAMPTIMESTAMP(Date [, Addition])
More about TIMESTAMP Function

TIMESTAMPADDTIMESTAMPADD(Unit, Number, Date)
More about TIMESTAMPADD Function

TIMESTAMPDIFFTIMESTAMPDIFF(Unit, Date 1, Date 2)
More about TIMESTAMPDIFF Function

UTC_DATEUTC_DATE()
More about UTC_DATE Function

UTC_TIMEUTC_TIME([Precision])
More about UTC_TIME Function

UTC_TIMESTAMPUTC_TIMESTAMP([Precision])
More about UTC_TIMESTAMP Function

DAYDAY(Date)
More about DAY Function

DAYOFMONTHDAYOFMONTH(Date)
More about DAYOFMONTH Function

DAYOFWEEKDAYOFWEEK(Date)
More about DAYOFWEEK Function

DAYOFYEARDAYOFYEAR(Date)
More about DAYOFYEAR Function

EXTRACTEXTRACT(Unit FROM Date)
More about EXTRACT Function

HOURHOUR(Date and Time)
More about HOUR Function

MICROSECONDMICROSECOND(Date and Time)
More about MICROSECOND Function

MINUTEMINUTE(Date and Time)
More about MINUTE Function

MONTHMONTH(Date)
More about MONTH Function

QUARTERQUARTER(Date)
More about QUARTER Function

SECONDSECOND(Date and Time)
More about SECOND Function

TIME_TO_SECTIME_TO_SEC(Time)
More about TIME_TO_SEC Function

TO_DAYSTO_DAYS(Date)
More about TO_DAYS Function

TO_SECONDSTO_SECONDS(Date and Time)
More about TO_SECONDS Function

UNIX_TIMESTAMPUNIX_TIMESTAMP([Date and Time])
More about UNIX_TIMESTAMP Function

WEEKWEEK(Date [, Mode])
More about WEEK Function

WEEKDAYWEEKDAY(Date)
More about WEEKDAY Function

WEEKOFYEARWEEKOFYEAR(Date)
More about WEEKOFYEAR Function

YEARYEAR(Date)
More about YEAR Function

YEARWEEKYEARWEEK(Date [, Mode])
More about YEARWEEK Function

DAYNAMEDAYNAME(Date)
More about DAYNAME Function

DATE_FORMATDATE_FORMAT(Date and Time, Format)
More about DATE_FORMAT Function

GET_FORMATGET_FORMAT(Type, Format)
More about GET_FORMAT Function

MONTHNAMEMONTHNAME(Date)
More about MONTHNAME Function

TIME_FORMATTIME_FORMAT(Time, Format)
More about TIME_FORMAT Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the SUBDATE SQL function: mysql.com and mariadb.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer