Oracle Communities

Oracle Analytics and AI

Child Item

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:

Date
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

Find more posts tagged with

Comments

JanardhanaKondaveeti

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