Jump to content
Sign in to follow this  
nickOS

Date calculation

Recommended Posts

I am trying to get the correct date calculation for my database.

I have a creation date , days allowed ( example 10 days ) and I need the new date field.

I am using date created + 10 = new date field.

Some of the dates are correct, some are wrong.

What I am doing wrong ?

Share this post


Link to post
Share on other sites

Some of the dates are correct, some are wrong.

Can you make an example of wrong dates ?

Share this post


Link to post
Share on other sites

example : date created 12-28-2008 + 10 days = 1/10/0001

Share this post


Link to post
Share on other sites

1/10/0001 ...

Rephrased ... it is no date and 10 days and the calculation result is date.

Edited by Guest
Rephrased

Share this post


Link to post
Share on other sites

Check that the date created field is a DATE field and that the new date field is a calculation field, result DATE

Share this post


Link to post
Share on other sites

If there is no date, you'll want to test for it and not evaluate, something like:

Case ( theDate ; theDate + 10 )

Share this post


Link to post
Share on other sites

1/10/0001 ...

Rephrased ... it is no date and 10 days and the calculation result is date.

Yes, that is !

So your calc must be:

Case( Start_date ; Start_date + days allowed )

Share this post


Link to post
Share on other sites

Thanks Laretta, I found the error. The result calculation should be a date.

Share this post


Link to post
Share on other sites

I found the error. The result calculation should be a date.

Well Nick, you said SOME records were incorrect and you presented:

[color:blue]Date of 12/28/2008 plus 10 days was generating 1/10/0001.

Let's consider:

  • If the calculation was a number and not date, it would 1) produce incorrect results in ALL records and not just some and 2) it would produce a number such as 733414 and not 1/10/0001 as you said it produced.

  • If the calculation was text or container and not a date, it would in fact produce the correct date result of 1/7/2009. And if a timestamp, it would produce 1/7/2009 12:00 AM.


  • If the calculation was time and not a date, it would produce 17601912:00:00.

I cannot see how simply changing the calculation type to date fixed this issue. :wink2:

Share this post


Link to post
Share on other sites

It actually did.

Calculation result was changed to date i/o number and also changed from stored to " do not store calculation results:

Share this post


Link to post
Share on other sites

I just want to be sure you now have the right results for the right reason. Because even changing it from number to date wouldn't make a difference; nor would changing it to unstored. In fact, none of the combinations you've suggested would make a difference in the outcome. It doesn't need to be unstored at all and would recalculate always anyway since the date and number fields are within the calculation and both reside in the same record. Or do they? The only way it would need to be unstored is if the date was Get ( CurrentDate ).

I'm not trying to be picky ... create a test file and check it - you will say everything I've told you works out exactly as described and 1/10/0001 cannot be reproduced by your actions unless the date is blank. I just want to be sure it is actually solved and not just fixed temporarily.

But as long as you are satisfied, I'll quit asking you to verify. SOMETHING fixed it, that's what counts. :wink2:

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.