This is an old revision of the document!

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.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference

Datetime Interval Expressions - IBM DB2 to SQL Server Migration

Mapping DB2 Interval Units to SQL Server DATEADD

Database and SQL Migration Tools

About SQLines