DATEDIFF Function - SQL Server to MariaDB Migration

In SQL Server, you can use DATEDIFF function to get the datetime difference in specified units. In MariaDB, you can use TIMESTAMPDIFF function.

Note that unit values can be different in SQL Server DATEDIFF and MariaDB TIMESTAMPDIFF.

SQL Server:

  -- Get difference in days
  SELECT DATEDIFF(dd, '2022-09-01', '2022-09-05'); 
  # 4

MariaDB:

  -- Get difference in days
  SELECT TIMESTAMPDIFF(day, '2022-09-01', '2022-09-05'); 
  # 4

Units Value Mapping

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

Unit SQL Server DATEDIFF MariaDB TIMESTAMPDIFF
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 as the start date. In this case it assumes 1900-01-01 date. You have to specify it explicitly in MariaDB, you cannot use an integer:

SQL Server:

  -- Get difference since 1900-01-01 in years (the second parameter is 0)
  SELECT DATEDIFF(yy, 0, '2022-09-05'); 
  # 122

MariaDB:

  -- Get difference since 1900-01-01 in years 
  SELECT TIMESTAMPDIFF(year, '1900-01-01', '2022-09-05'); 
  # 122

For more information, see SQL Server to MariaDB Migration