CODEWITHSUNDEEP
oracle-databasefunctiondatetimeprocedure
Joel Jacobson
Joel Jacobson

Reputation: 145

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.

Upvotes: 0

Views: 489

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

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)))

Upvotes: 1

Related Questions