Ispirer Website
Ispirer Capabilities: Microsoft SQL Server Migration
Free Trial
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