TRUNC - Truncate Datetime - Oracle to SQL Server Migration

In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e).

In SQL Server, you can use various expressions using CONVERT function to get the same result.

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Get current datetime with the time set to zero
  SELECT TRUNC(SYSDATE) FROM dual;
  # 2013-02-11 00:00:00

SQL Server:

  -- Get current datetime with the time set to zero 
  SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()));
  # 2013-02-11 00:00:00.000

Oracle TRUNC for datetime to SQL Server conversion:

TRUNC function supports the following truncation units:

Conversion summary:

Oracle	SQL Server
TRUNC(exp [,'DD'])	CONVERT(DATETIME, CONVERT(DATE, exp))	Since SQL Server 2008
	CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120))
TRUNC(exp, 'MM')	CONVERT(DATETIME, CONVERT(VARCHAR(7), exp, 120) + '-01')
TRUNC(exp, 'YY')	CONVERT(DATETIME, CONVERT(VARCHAR(4), exp, 120) + '-01-01')
TRUNC(exp, 'HH24')	CONVERT(DATETIME, CONVERT(VARCHAR(13), exp, 120) + ':00:00')
TRUNC(exp, 'MI')	CONVERT(DATETIME, CONVERT(VARCHAR(16), exp, 120) + ':00')

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

By default, Oracle TRUNC truncates a datetime to day (sets the zero time):

Oracle:

  -- Get the current datetime with time set to zero 
  SELECT TRUNC(SYSDATE) FROM dual;
  # 2013-02-11 00:00:00
 
  SELECT TRUNC(SYSDATE, 'DD') FROM dual;
  # 2013-02-11 00:00:00

In SQL Server, you can firstly convert a datetime to DATE that does not contain the time part, and then convert it back to DATETIME or DATETIME2. The time part will be set to zero now:

SQL Server:

  -- Get the current datetime with time set to zero 
  SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()));
  # 2013-02-11 00:00:00.000

Note that DATE data type is available since SQL Server 2008

You can also convert a datetime to VARCHAR(10) to take the first 10 characters, use style 120 to get the 'YYYY-MM-DD' format, and then convert back to DATETIME or DATETIME2. The time part will be zero as well:

SQL Server:

  -- Get the current datetime with time set to zero 
  SELECT CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120));
  # 2013-02-11 00:00:00.000

If you specify 'MM' or 'MONTH' unit in Oracle TRUNC, the datetime is truncated to month (day is set to 1, and time is set to 00:00:00):

Oracle:

  -- Get the current year and month, day is set to 1 and time is set to zero 
  SELECT TRUNC(SYSDATE, 'MM') FROM dual;
  # 2013-02-01 00:00:00

In SQL Server, you can convert a datetime to VARCHAR(7) using style 120 to get the YYYY-MM format, then add '-01', and convert to DATETIME or DATETIME2:

SQL Server:

  -- Get the current year and month, day is set to 1 and time is set to zero 
  SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), GETDATE(), 120) + '-01');
  # 2013-02-01 00:00:00.000

If you specify 'YY' or 'YEAR' unit in Oracle TRUNC, the datetime is truncated to year (month and day are set to 1, and time is set to 00:00:00):

Oracle:

  -- Get the first day of the current year 
  SELECT TRUNC(SYSDATE, 'YY') FROM dual;
  # 2013-01-01 00:00:00

In SQL Server, you can convert a datetime to VARCHAR(4) using style 120 to get the year, then add '-01-01', and convert to DATETIME or DATETIME2:

SQL Server:

  -- Get the first day of the current year 
  SELECT CONVERT(DATETIME, CONVERT(VARCHAR(4), GETDATE(), 120) + '-01-01');
  # 2013-01-01 00:00:00.000

You can also use YEAR or DATEPART function to get the year, convert it to string, add '-01-01' and then convert to DATETIME or DATETIME2:

SQL Server:

  -- Get the first day of the current year 
  SELECT CONVERT(DATETIME, CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01')
  # 2013-01-01 00:00:00.000
 
  SELECT CONVERT(DATETIME, CAST(DATEPART(yy, GETDATE()) AS VARCHAR) + '-01-01')
  # 2013-01-01 00:00:00.000

If you specify 'HH24' or 'HH' unit in Oracle TRUNC, the datetime is truncated to hour (minutes and seconds are set to 00:00):

Oracle:

  -- Get the current datetime with the hour accuracy 
  SELECT TRUNC(SYSDATE, 'HH24') FROM dual;
  # 2013-02-11 13:00:00

In SQL Server, you can convert a datetime to VARCHAR(13) using style 120 to get the 'YYYY-MM-DD HH24' format, add ':00:00', and convert to DATETIME or DATETIME2:

SQL Server:

  -- Get the current datetime with the hour accuracy 
  SELECT CONVERT(DATETIME, CONVERT(VARCHAR(13), GETDATE(), 120) + ':00:00');
  # 2013-02-11 13:00:00.000

If you specify 'MI' unit in Oracle TRUNC, the datetime is truncated to minute (seconds are set to zero):

Oracle:

  -- Get the current datetime with the minute accuracy 
  SELECT TRUNC(SYSDATE, 'MI') FROM dual;
  # 2013-02-11 13:49:00

In SQL Server, you can convert a datetime to VARCHAR(16) using style 120 to get the 'YYYY-MM-DD HH24:MI' format, add ':00', and convert to DATETIME or DATETIME2:

SQL Server:

  -- Get the current datetime with the minute accuracy 
  SELECT CONVERT(DATETIME, CONVERT(VARCHAR(16), GETDATE(), 120) + ':00');
  # 2013-02-11 13:49:00.000

• Oracle to SQL Server Migration Knowledge Base

Oracle 11g R2 SQL Language Reference

• TRUNC For Datetime Function

Microsoft SQL Server 2012 - Books Online

• CAST and CONVERT Functions
• YEAR Function
• DATEPART Function

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.