Datetime Interval Expressions - IBM DB2 to SQL Server Migration

In DB2, you can use interval expressions to add the specified number of units to a datetime value, for example:

DB2:

  -- Add 3 months to the current date
  CURRENT_DATE + 3 MONTHS

In SQL Server, you can use the DATEADD function to add interval units to a datetime:

SQL Server:

  -- Add 3 months to the current date
  DATEADD(mm, 3, CONVERT(DATE, GETDATE()))

Mapping DB2 Interval Units to SQL Server DATEADD

To convert DB2 interval expressions you have to map interval units to the corresponding datepart values of DATEADD function in SQL Server:

Datetime interval expressions:

DB2 Interval Unit SQL Server DATEADD Datepart
1 MONTH | MONTHS Interval in months mm
2 DAY | DAYS Interval in days dd
3 MINUTE | MINUTES Interval in minutes mi
4 SECOND Interval in seconds ss

Examples:

DB2 SQL Server
1 datetime + expr MONTH Add months DATEADD(mm, expr, datetime)
2 datetime - expr MONTH Subtract months DATEADD(mm, -expr, datetime)
3 datetime + expr DAYS Add days DATEADD(dd, expr, datetime)
4 datetime - expr DAYS Subtract days DATEADD(dd, -expr, datetime)
5 datetime + expr MINUTES Add minutes DATEADD(mi, expr, datetime)
6 datetime - expr MINUTES Subtract minutes DATEADD(mi, -expr, datetime)
7 datetime +expr SECOND Add seconds DATEADD(ss, expr, datetime)
8 datetime - expr SECOND Subtract seconds DATEADD(ss, -expr, datetime)

Database and SQL Migration Tools

About SQLines

SQLines offers services and open source tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

You could leave a comment if you were logged in.