Problem with adding days in a date(sql)

Saswati Singh

Summary

Problem with adding days in a date (sql)

Content

Hi All,

I’m building a bi report and I have the following problem when building the data model.

Oracle provides ADD_MONTHS() function to use when adding duration to a date the function takes two parameters:

1. Date
2. Integer                                                                                                                                                              

i.e. ADD_MONTHS(28- Feb-2017,5) 

More over if you directly add a number to a date like (‘23 - Mar – 2017’)+ 40 this is automatically taken as adding 40 days to that date.

What can I do if the duration I want to add is in a decimal value e.g. 8.9 months because the ADD_MONTHS() function fail in this case?

 

 Since the duration is not an integer so I'm failing to get a precise value in this case

8.9 will be rounded to 8 and it will add 8 months instead of 8.9 months.

 

 

Clear Question

How to add a decimal duration value of months using add_months() function and get a precise answer when building a data model?

 

Thanks,

Saswati Banik

Janardhana K

Hi,

I think it works as designed.

Look here; http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions011.htm#SQLRF00603

 

Thanks,
Janardhan

Jorge Orrillo

why don't you translate 8.9 months to 267 days?.... that way you can do:

TIMESTAMPADD(SQL_TSI_DAY ,267, CURRENT_DATE) -> This adds 267 days to today's date.

 

I hope this helps.

 

Jorge O.

Saswati Singh

Thanks for the response

 

@Jonardhan, So does this mean is totally not posible to add a decimal number of months to a date in BI publisher?

 

@Jorge O, BI Publisher does not accept TIMESTAMPADD() function ,this function is ONLY accepted in OTBI work area,and converting months todays Im facing a problem with multiplication  since the number of days in all months is not fixed.

Thanks,

Saswati