DATEADD Function - SQL Server to MySQL Migration

In SQL Server, you can use DATEADD function to add an interval in the specified units to the datetime value. In MySQL, you can use TIMESTAMPADD function.

Note that unit values can be different in SQL Server DATEADD and MySQL TIMESTAMPADD functions.

SQL Server:

  -- Add 3 days
  SELECT DATEADD(dd, 3, '2022-09-06'); 
  # 2022-09-09 00:00:00.000

MySQL:

  -- Add 3 days
  SELECT TIMESTAMPADD(day, 3, '2022-09-06'); 
  # 2022-09-09

Units Value Mapping

You can map unit values between SQL Server DATEADD and MySQL TIMESTAMPADD functions as follows:

Unit SQL Server DATEADD MySQL TIMESTAMPDD
Year year, y, yy, yyyy year
Quarter quarter, qq, q quarter
Month month, mm, m month
Dayofyear dayofyear, dy
Day day, dd, d day
Week week, wk, ww week
Hour hour, hh hour
Minute minute, mi, n minute
Second second, ss, s second
Millisecond millisecond, ms Use microsecond and divide by 1000
Microsecond microsecond, mcs microsecond
Nanosecond nanosecond, ns

Some Special Values

In SQL Server you can specify an integer instead of the datetime expression. In this case it assumes the number of days since 1900-01-01 and can be negative.

SQL Server:

  -- Add 1 day since 1900-01-01
  SELECT DATEADD(dd, 1, 0); 
  # 1900-01-02 00:00:00.000
 
    -- Add 1 day since 1900-01-01 - 10 days
  SELECT DATEADD(dd, 1, -10); 
  # 1899-12-23 00:00:00.000

You have to specify the datetime explicitly in MySQL, you cannot use an integer:

MySQL:

    -- Add 1 day since 1900-01-01
  SELECT TIMESTAMPADD(day, 1, '1900-01-01'); 
  # 1900-01-02
 
    -- Add 1 day since 1900-01-01 - 10 days
  SELECT TIMESTAMPADD(day, 1, ADDDATE('1900-01-01', -10)); 
  # 1899-12-23

For more information, see SQL Server to MySQL Migration.