TRUNC (date) Function Conversion from Oracle to Microsoft SQL Server

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Microsoft SQL Server does not support TRUNC function. Therefore, need to create an additional object, namely a function that simulates the operation of the TRUNC function.

Let's create the following function and see how it works:

 create function dbo.SWF_TruncDate(@pDate datetime, @FmtStr varchar(255))
 returns datetime
 begin
   IF (UPPER(@FmtStr) = 'YEAR' or UPPER(@FmtStr) = 'YY' or UPPER(@FmtStr) = 'YYYY' or UPPER(@FmtStr) = 'Y' or UPPER(@FmtStr) = 'YYY')
     RETURN convert(varchar,year(@pDate))+'-01-01'
   ELSE IF (UPPER(@FmtStr) = 'MONTH' or UPPER(@FmtStr) = 'MM' or UPPER(@FmtStr) = 'MON')
     RETURN convert(varchar,year(@pDate))+'-'+convert(varchar,month(@pDate))+'-01'
   ELSE IF (UPPER(@FmtStr) = 'DD' or UPPER(@FmtStr) = 'DDD')
     RETURN convert(varchar,@pDate,111)
   ELSE IF (UPPER(@FmtStr) = 'D' or UPPER(@FmtStr) = 'DY' or UPPER(@FmtStr) = 'DAY')
     RETURN convert(datetime,convert(varchar,@pDate,111))
   ELSE IF (UPPER(@FmtStr) = 'HOUR' or UPPER(@FmtStr) = 'HH' or UPPER(@FmtStr) = 'HH12' or UPPER(@FmtStr) = 'HH24')
     RETURN convert(varchar,@pDate,111)+' '+convert(varchar,datepart(hh,@pDate))+':00:00'
   ELSE IF (UPPER(@FmtStr) = 'MINUTE' or UPPER(@FmtStr) = 'MI')
     RETURN convert(varchar,@pDate,111)+' '+convert(varchar,datepart(hh,@pDate))+':'+convert(varchar,datepart(mi,@pDate))+':00'
 
   RETURN @pDate
 end

The function checks FmtStr parameter and if it is 'YEAR'/'YY'/'YYYY'/'Y'/'YYY', it will take the year specified in pDate parameter and concat it with '-01-01' (January 1). If FmtStr parameter is 'MONTH'/'MM'/'MON', the function will return the concatenation of the year, month from pDate parameter and '-01', that is, start of month. When FmtStr parameter is 'DD'/'DDD'/'D'/'DY'/'DAY', the function will return date truncated to the day. 'DD' is a default value when FmtStr parameter is not specified in source. If FmtStr parameter is 'HOUR'/'HH'/'HH12'/'HH24', the return value will be date truncated to the hour. And if FmtStr parameter is 'MINUTE'/'MI', the return value will be date truncated to the minute.

Let's look at an example of how such function would work:

Oracle	Microsoft SQL Server
select trunc(TO_DATE('25-AUG-22','DD-MON-YY')) from dual	select dbo.SWF_TruncDate(CONVERT(DATETIME,'25-AUG-22'),'DD')
select trunc(TO_DATE('25-AUG-22','DD-MON-YY'), 'YY') from dual;	select dbo.SWF_TruncDate(CONVERT(DATETIME,'25-AUG-22'),'YY')
select trunc(TO_DATE('25-AUG-22','DD-MON-YY'), 'MONTH') from dual;	select dbo.SWF_TruncDate(CONVERT(DATETIME,'25-AUG-22'),'MONTH')
select trunc(TO_DATE('25-AUG-22','DD-MON-YY'), 'DDD') from dual;	select dbo.SWF_TruncDate(CONVERT(DATETIME,'25-AUG-22'),'DDD')

As you can see the TRUNC function has been replaced by created SWF_TruncDate function. You can check and make sure that the queries are equivalent and return the same result set.

This solution is automatically executed by our software Ispirer Toolkit. You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.

If you have any questions or face any difficulties, please contact our support team: support@ispirer.com