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:
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:
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
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
SQL Server:
SELECT CONVERT(DATETIME, CONVERT(DATE, GETDATE()));
Gives Error: Type DATE is not a defined system type.