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

TRUNC Conversion Overview

Oracle TRUNC for datetime to SQL Server conversion:

Oracle SQL Server
Syntax TRUNC(datetime [, unit]) CONVERT expressions depending on unit
Default Unit 'DD' truncates to day (sets zero time)

TRUNC function supports the following truncation units:

Oracle TRUNC Unit Truncation Result
'DD' 'DDD' Day YYYY-MM-DD 00:00:00
'MM' 'MONTH' 'MON' Month YYYY-MM-01 00:00:00
'YY' 'YEAR' Year YYYY-01-01 00:00:00
'HH' 'HH24' Hour YYYY-MM-DD HH:00:00
'MI' Minute YYYY-MM-DD HH:MI:00

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')

Converting Oracle TRUNC to SQL Server

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

Truncate to Month

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

Truncate to Year

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

Truncate to Hour

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

Truncate to Minute

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

Database and SQL Migration Tools

About SQLines

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

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.

Discussion

, March 02, 2014 6:21 am

SQL Server:

  1. - Get current datetime with the time set to zero

SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()));

Gives Error: Type DATE is not a defined system type.

You could leave a comment if you were logged in.