Convert TRUNC from Oracle to SQL Server |
| [Oracle to MS SQL Converter] [Tutorial] [Database Migration Service] |
Oracle provides the function TRUNC(datetime, part) to truncate a datetime value to the specified part. For example, it can set zero time or first day of the month. If second argument 'part' is omitted, the function truncates given datetime value to days or in other words sets zero time:
SQL> select trunc(systimestamp) as trunc from dual;TRUNC --------- 16-MAY-23
The table below contains list of Oracle supported parts of truncation and MS SQL equivalents for conversion:
| TRUNC Part | Truncation | MS SQL Equivalent |
| 'MI' | YYYY-MM-DD HH:MI:00 | DATEADD(minute, DATEDIFF(minute, 0, @yourdate), 0) |
| 'HH' or 'HH24' | YYYY-MM-DD HH:00:00 | DATEADD(hour, DATEDIFF(hour, 0, @yourdate), 0) |
| 'DD' or 'DDD' | YYYY-MM-DD 00:00:00 | CAST(CAST(@yourdate as date) as datetime) |
| 'MM' or 'MONTH' | YYYY-MM-01 00:00:00 | DATEADD(month, DATEDIFF(month, 0, @yourdate), 0) |
| 'YY' or 'YEAR' | YYYY-01-01 00:00:00 | DATEADD(year, DATEDIFF(year, 0, @yourdate), 0) |
Specialists of Intelligent Converters offer Database Migration Service to help you migrate SQL and PL/SQL from Oracle to SQL Server or Azure SQL.