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 HOURS	Add days	DATEADD(hh, expr, datetime)
6	datetime - expr HOURS	Subtract days	DATEADD(hh, -expr, datetime)
7	datetime + expr MINUTES	Add minutes	DATEADD(mi, expr, datetime)
8	datetime - expr MINUTES	Subtract minutes	DATEADD(mi, -expr, datetime)
9	datetime +expr SECOND	Add seconds	DATEADD(ss, expr, datetime)
10	datetime - expr SECOND	Subtract seconds	DATEADD(ss, -expr, datetime)

Database and SQL Migration Tools

You could leave a comment if you were logged in.