SQLTeam.com | Weblogs | Forums

ADD_MONTH() to 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?


#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: