(_days_between (end_date,begin_date)) One Day Off in Calculation in Report Studi

[gosoccer]

Hi,
Good afternoon,
It seems like the following function is short of one day so I had to add a +1 at the end
to show the right amount of days between the Begin and End date,
(_days_between (end_date,begin_date+1)

Does anyone know a different option to take that is better than adding one day to the end?
Thanks in advance for your help!!

[BigChris]

Are begin_date and end_date dates, or are they datetimes?

[ankur10]

Even i feel the same, when i used the current_date it was working fine. However as i wanted to have days in between, so i have taken the same.

[gosoccer]

Hi Chris,
The begin_date and end_date dates are just Date Data Type and not Date with the Time Stamp?

So the Begin Date goes after filtering the rows based on the C_DATE that is > Begin Date and the C_DATE
that is smaller than equal End Date.

Thanks for your time.

[Stepharia]

Can you please provide an example of what you are expecting
e.g. if begin date=1/9/17 and end date=2/9/17 would you expect days between to be 1 or 0 or 2
(in the example above 1 is what _days_between would return on its own)
You may need to use the _add_days function on one of the dates to achieve the desired result.

[gosoccer]

Stepharia,

That is a great question. I think you are on to something that I have been worried about since I don't think my code with the +1 is solving this issue either.

Explanation:
The business need was to identify/report the CARS that are in the garage for more than 30 days.  All past 30 days car are presented in the report EXCEPT one, a CAR that was 1 day late and is therefore not present due to the report calculation being one day off (the report thought this was on-time)
Older  Code:
(_days_between (end_date,begin_date))

Newer Code:
(_days_between (end_date,begin_date+1)

I appreciate your time very much!

Let me know what you think?

[gosoccer]

Additional explanations:

One more thing. To answer your question correctly, for the "begin date=1/9/17 and end date=2/9/17 would you expect days between to be 1 or 0 or 2. I think 1 is the answer. They just wanted to include the begin date, as One Day. It seems like the issue is that it is not counting the begin date as day #1 – therefore all counts are one day off.

So, do you think the (_days_between (end_date,begin_date+1) is leaving out the begin day as ONE day calculation using the +1?

Thanks again for your time. :) :)

[BigChris]

The _days_between function gives you the elapsed days between the two dates. So to extend the example if you'd got

Code Select Expand

_days_between (2017-09-01,2017-09-01)

you should get an answer of zero days. If you're expecting 2017-09-01 to count as the first day, i.e. to count as 1 and 2017-09-02 to count as 2 etc. then you will need to add 1 day to the calculation.

[gosoccer]

I should test this myself by using the code but do you think based on your experience the below code is adding the one day,
(_days_between (end_date,begin_date+1)

Or I should use _add_days function?

Thanks alot!

[BigChris]

I would have thought you'd either want

Code Select Expand

_days_between (end_date, begin_date) + 1

or

Code Select Expand

_days_between (_add_days(end_date,1),begin_date)

[gosoccer]

Thanks so much!

[ankur10]

Hello Everyone,

Sorry to jump in. Just wanted to know how to calculate date from the beginning of time till today. Like they want to have all the data from the beginning of time when they started till today. As between will not work then how to get it. Please guide. Thank you 

[New_Guy]

Hi Ankur10,
Have you tried the filter condition with less then or equal to, like the one below,
[Time column] <= current_date
Good luck
New guy