ADD_MONTH() to SQL

Transact-SQL
1

Hi ,

I have below conversion from Oracle to SQL can you please chk and let me know if i did the conversion correct?
Thanks!

--ADD_MONTHS(A.ENTRY_DT, ST.INCR_TIME * 6 ) , (ADD_MONTHS(A.ENTRY_DT, ST.INCR_TIME * 6 ) ) - SYSDATE , --- Oracle

SQL

DATEADD(month,A.ENTRY_DT, ST.INCR_TIME *6), (DATEADD(month,A.ENTRY_DT, ST.INCR_TIME * 6 ) ) - GETDATE() ,

2

No, it's not correct.

You need to lookup the syntax for the SQL DATEADD command:
DATEADD(MONTH, <# of months>, date_to_add_months_to)

3

I did, I was trying that format but only take 3 arguments, In my example there are 2 date fields, A.ENTRY_DT, ST.INCR_TIME , so I don't know how to include these 2 dates in the formula/query?

The dateAdd function returns integer but the fields are date?

Thanks.

4

what have you tried?

5

I tried breaking it up with one field but not sure how to do it with 2 fields? I used below short query to test it out but when I add *6 I get error " Operand type clash: date is incompatible with int" which is looking for INT. It works when I remove *6 and I get the field with added one month.

SELECT top 10 A.ENTRY_DT,
dateadd(month,1,A.STEP_ENTRY_DT) *6
FROM
EMPLOYEE

Now I need to do add one more field to this formula: BOLDED below

DATEADD(month,A.ENTRY_DT, ST.INCR_TIME *6), (DATEADD(month,A.ENTRY_DT, ST.INCR_TIME * 6 ) ) - GETDATE() ,

Thanks!

6

Pasi,

What does the documentation for DATEADD say about the data type it returns?

https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql

7

I know it returns a date and INT, but I don't know how to construct this with 2 date fields? Hope I am clear?
Thanks.

8

so why are you multiplying a date by 6? trial and error will not get you the result you want :slight_smile: please provide the dates you are working with and the final result you would like to see

9

OK, this reports are old in Oracle and field Incr_TIME is an INT which when is multiplied by 6 gives you a year from the ENTRY_DT date. so basically the report is for up coming step increase for employees.
in below case from Oracle the 58 days is for next increase other formulas that creates a date from Entry_DT. hope this helps?

image
image.png1090×113 35.9 KB

10

what if DM_INCR_TIME is 2 ,3 , 4?

11 
;with ctePasi
as
(
	select '2017-09-17' as STEP_ENTRY_DT, 1 DM_INCR_TIME
	union all
	select '2017-09-17' as STEP_ENTRY_DT, 1 DM_INCR_TIME
	union all
	select '2017-09-17' as STEP_ENTRY_DT, 1 DM_INCR_TIME
)
select DATEADD(m, DM_INCR_TIME * 6, STEP_ENTRY_DT), 
DATEDIFF (day, Getdate(), DATEADD(m, DM_INCR_TIME * 6, STEP_ENTRY_DT) ) 
  from ctePasi
12

Thanks if its 2, 3, 4 then it gets multiplied by 6 again.

13

yosiasz, Just a question as why to use CTE? is there other way just to formulate this? Just wondering?
Thanks.
Pasi.

14

cte because you did not provide sample data. that's all

15

Ok, only thing I could show was the screen shot as how its been calculated in oracle.
Pasi.

16

you could have provided sample data also. so screen shot is not the only thing you could have shown us :slight_smile: always provide sample data in the form of a cte or

create table #sampledata(step_entry_dy date, dm_incr_time int)

insert into #sampledata
select '2017-09-17' as STEP_ENTRY_DT, 1 DM_INCR_TIME
	union all
	select '2017-09-17' as STEP_ENTRY_DT, 1 DM_INCR_TIME
	union all
	select '2017-09-17' as STEP_ENTRY_DT, 1 DM_INCR_TIM

because we cant scrape data from a screen shot :slight_smile: