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


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

Units Value Mapping

You can map unit values between SQL Server DATEDIFF and MySQL TIMESTAMPDIFF functions as follows:

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 3 days 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:


    -- Add 1 day since 1900-01-01
  SELECT TIMESTAMPADD(day, 1, '1900-01-01'); 
  # 1900-01-02
    -- Add 3 days 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