TIMESTAMPADD to DATEADD - Sybase Advantage to SQL Server Migration

In Sybase Advantage Database you can use TIMESTAMPADD function to add an interval to the datetime value. In Microsoft SQL Server you can use DATEADD function, but note that the interval unit specifiers are different:

Sybase ADS:

  -- Add 1 day to the current datetime
  SELECT TIMESTAMPADD(SQL_TSI_DAY, 1, CURRENT_TIMESTAMP());
  # 2017-12-30 17:27:11.343
 
  -- Subtract 1 day from the current datetime
  SELECT TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_TIMESTAMP());
  # 2017-12-28 17:27:11.343

SQL Server:

  -- Add 1 day to the current datetime
  SELECT DATEADD(dd, 1, GETDATE())
  # 2017-12-30 17:27:11.343
 
  -- Subtract 1 day from the current datetime
  SELECT DATEADD(dd, -1, GETDATE())
  # 2017-12-28 17:27:11.343

Mapping Sybase ADS TIMESTAMPADD Interval Units to SQL Server

You can use SQLines SQL Converter to convert Sybase ADS TIMESTAMPADD function to DATEADD that maps the interval units to the appropriate values in SQL Server:

Sybase ADS SQL Server
SQL_TSI_YEAR Interval in years yy
SQL_TSI_QUARTER Interval in quarters qq
SQL_TSI_MONTH Interval in months mm
SQL_TSI_WEEK Interval in weeks wk
SQL_TSI_DAY Interval in days dd
SQL_TSI_HOUR Interval in hours hh
SQL_TSI_MINUTE Interval in minutes mi
SQL_TSI_SECOND Interval in seconds ss
SQL_TSI_FRAC_SECOND Interval in milliseconds ms

Conversion examples:

Sybase ADS SQL Server
1 TIMESTAMPADD(SQL_TSI_MINUTE, 3, CURRENT_TIMESTAMP()) DATEADD(mi, 3, GETDATE())
2 TIMESTAMPADD(SQL_TSI_HOUR, 3, CURRENT_TIMESTAMP()) DATEADD(hh, 3, GETDATE())
3 TIMESTAMPADD(SQL_TSI_MONTH, 3, CURRENT_TIMESTAMP()) DATEADD(mm, 1, GETDATE())

For more information, see: