Search code examples
oracle-databasefunctiondatetimeprocedure

Using TRUNC function from Oracle in T-SQL


So I'm in the process of doing Procedures and Functions conversion from Oracle to MS SQL Server 2012.

In Oracle I have the following select statement:

--Add 5 months less one day to the quarter, ie 1/1/09 returns 5/31/09 
SELECT ADD_MONTHS(TRUNC(to_date('1-dec-2017'), 'q'), 5) - 1  FROM DUAL;

And I get the following value in return:

28-FEB-18

I am trying to convert it into MS SQL Server like:

SELECT dateadd(m, 0, convert(datetime, dateadd(q, 5,convert(datetime,'1-dec-2017')))) - 1

And I get the following result: 2019-02-28 00:00:00.000

I am not sure why I am getting an additional year in T-SQL. I will appreciate some assistance. thank you.


Solution

  • To truncate to quarter, see What is the best way to truncate a date in SQL Server?

    SELECT DATEADD(Q, DATEDIFF(Q, 0, convert(datetime,'1-dec-2017')), 0)
    

    Add five months.

    SELECT DATEADD(M, 5, DATEADD(Q, DATEDIFF(Q, 0, convert(datetime,'1-dec-2017')), 0))
    

    Subtract a day

    SELECT DATEADD(M, 5, DATEADD(Q, DATEDIFF(Q, 0, convert(datetime,'1-dec-2017')), 0)) - 1
    SELECT DATEADD(D, -1, DATEADD(M, 5, DATEADD(Q, DATEDIFF(Q, 0, convert(datetime,'1-dec-2017')), 0)))