Add_month Convert to SQL

Transact-SQL
1

Hi ,

I have a oracle query to convert to SQL, whats the best way to do this?
TRUNC ( ( ADD_MONTHS(A.END_DT - 1 , 7) ), 'MONTH') . end_date is a DATE field.

basically taking the end date subtract 1 month and add 7 month to it.

Thanks!
Pasi.

2

provide sample data

3

cast(datepart(month,dateadd(month,7,dateadd(day,-1,a.end_dt))) as varchar(2))+' MONTH'

4

Thanks bitsmed. I will try this, I dont have any sample data currently, as I am working to build data. I will try to get some.
Pasi

5

It would help if you did a bit of research on these before asking. I have seen a lot of questions from you that can be easily answered with a bit of help from google...

Okay - let's take a look at what this is actually doing:

TRUNC({date value}, 'MONTH') returns the first day of the month for the specified {date}

Example: TRUNC('2017-01-22', 'MONTH') returns '2017-01-01'

The next portion of the code is:

A.END_DT - 1

This doesn't subtract 1 month - it subtracts 1 day from the end date. So - assuming the end date is any date except the first of a month it really isn't going to change anything.

The next item is: ADD_MONTHS({date}, 7) which adds 7 months to the date passed...

If the END_DT is '2017-01-22' we have this:

TRUNC(( ADD_MONTHS('2017-01-22' - 1, 7)), 'MONTH')
TRUNC(( ADD_MONTHS('2017-01-21', 7)), 'MONTH')
TRUNC(( '2017-08-21', 'MONTH')
'2017-08-01'

If the END_DT is the first of the month:

TRUNC(( ADD_MONTHS('2017-01-01' - 1, 7)), 'MONTH')
TRUNC(( ADD_MONTHS('2016-12-31', 7)), 'MONTH')
TRUNC(( '2017-07-31', 'MONTH')
'2017-07-01'

In SQL Server:

dateadd(month, datediff(month, 0, dateadd(month, 7, dateadd(day, -1, '2017-01-22'))), 0)
dateadd(month, datediff(month, 0, dateadd(month, 7, dateadd(day, -1, '2017-01-01'))), 0)

If you want this as a DATE data type and not a DATETIME:

cast(dateadd(month, datediff(month, 0, dateadd(month, 7, dateadd(day, -1, '2017-01-22'))), 0) as date)
cast(dateadd(month, datediff(month, 0, dateadd(month, 7, dateadd(day, -1, '2017-01-01'))), 0) as date)

Another approach is this:

dateadd(day, 1, eomonth(dateadd(day, -1, '2017-01-22'), -6))
dateadd(day, 1, eomonth(dateadd(day, -1, '2017-01-01'), -6))

This one returns a date without using the cast and may perform better - it is also shorter and uses less functions to get the result which definitely should improve performance but you have to test.

6

Thank you for response, I usually search google a lot before posting, Ive seen examples and tried few things before I post on here. I am still learning the conversions and I am not a guru in SQL conversions that's why I post here to learn.

on A.END_DT - 1 I thought it was subtracting a month, no idea it was a day! thanks on that. Believe me I tried everything and used so many version of convert but what you have here is accurate. Thanks again.
P.

7

Since I don't have access to any Oracle systems - I have to search for what each command does before I can figure out what the corresponding T-SQL would be...

So when you post the command - but we have no idea what the result of that command is or how it works we have to make assumptions that may not be correct. You will have some people asking for sample data, others may post incorrect solutions and yet others may just ignore the post altogether.

What I meant with doing a little research is to do what I did in my previous post...

I googled for how to add to a date in Oracle - this returned several results that showed how to add a day (e.g. A.END_DT - 1).

I googled the function ADD_MONTHS to be sure I understood how that actually works...
I googled TRUNC to be sure I had an idea on how that works...

So - the question you should have been asking was:

How do I get the first day of the month - 6 months from the given date? And the sample data would just be a given date and the expected output showing the first of the month 6 months from that date (adjusted by one day prior to the given date).

Note: this is incorrect

dateadd(day, 1, eomonth(dateadd(day, -1, '2017-01-22'), -6))
dateadd(day, 1, eomonth(dateadd(day, -1, '2017-01-01'), -6))

It should be:

dateadd(day, 1, eomonth(dateadd(day, -1, '2017-01-22'), 6))
dateadd(day, 1, eomonth(dateadd(day, -1, '2017-01-01'), 6))

8

Thanks Jeff, good points, as I mentioned the End_DT -1 threw me off. I looked at every individual part of the commands and below was the closest I came up with but was not working: I am not too familiar with Oracle as well but learning as I go through them.

Cast(datepart(month,dateadd(month,7,dateadd(MONTH,-1,a.END_dt))) as datetime)

Any way thanks for help!
Pasi.