cancel
Showing results for 
Search instead for 
Did you mean: 

BOXI3.0: Formula for subtract/deduct one day or more from date field

Former Member
0 Kudos

Greetings.

Hello there. Alias here.

I have one question.

Example I have Order Date field and from this field I want to subtract the date by minus one day or two days or three days, etc.

How should I do that?

Best regards,

Alias

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Greetings.

As I mentioned that i got the solution:

DATEADD (dd , -3, EPJ_IPMS.dbo.DO_DATA.DO_DATE_D)

But this is at designer level. At report/webi level it does not work.

I also do not understand why at designer and webi level may have different SQL select statement.

Can someone help me please.

Best Regards,

Alias

Former Member
0 Kudos

Alias,

But this is at designer level. At report/webi level it does not work.

At WebI level you have to use a different function (and syntax). You should repost your topic in the WebI forum along with the formula you are trying to use so that someone can help you further.

I also do not understand why at designer and webi level may have different SQL select statement.

Having different SQL select statements is unusual, however, it is possible. If you are using two different connections from one universe to another, and one connection uses a different version of ODBC than another connection, this can happen. One way to check for consistent SQL select statements is to use the Query Panel in Designer and build a mock up of your query (i.e. include the objects and filters that you would use in WebI, then view/copy/paste that resultant SQL statement). Next, go to WebI and perform the same activity and click on the "SQL" button to view the generated SQL -- both SQL statements should be the same, if not, then you'll have to investigate the Data Sources (ODBC) settings on the server to ensure you are using the correct version of ODBC.

Thanks,

John

amrsalem1983
Active Contributor
0 Kudos

if you want to do that in the designer, you should find the DBMS function which do this job, and put it in a new dimension.

in oracle we say

yesterday = mydate - 1 as you see minues one means the day before the date

you can substract dates directly by numbers.

and i think it works for the other DBMSs.

good luck

Amr

Former Member
0 Kudos

Hi there Amr.

You mean DBMS is Database Management System?

Well, I am using SQL.

The initial select statement is as below:

EPJ_IPMS.dbo.DO_DATA.DO_DATE_D

    • the result is example 08/28/2008 12:00:00 AM

Then I change the select statemetn as below:

CONVERT(VARCHAR(8),EPJ_IPMS.dbo.DO_DATA.DO_DATE_D,112)

    • the result is example 20080321

Next, what I would like to do is to example minus 20080321 by 3 days.

    • the result should be 20080318.

This is where I am stuck.

Best Regards,

Alias

Former Member
0 Kudos

I think I got the solution and wanna share to you all.

DATEADD (dd , -3, EPJ_IPMS.dbo.DO_DATA.DO_DATE_D)

    • EPJ_IPMS.dbo.DO_DATA.DO_DATE_D ** --> change to the date field of your table

Best regards,

Alias

amrsalem1983
Active Contributor
0 Kudos

as i told you Alias its different from one database to another

everyone and it has its own functions to handle those things,

as you see in oracle we do it directly, we dont use DATEADD,

we have only ADDMONTHS to add a specific number of months to the date.

anyway, good luck

Amr