6 Replies Latest reply on Jul 3, 2014 11:52 AM by Emmanuel

# Check if date between two dates

### Title

Check if date between two dates

### Post

Hi,

I got this calculated field:

Case (

Date (8;1;CurrentYear) ≤ _date inscription  ≤ Date (7;31;CurrentYear+1) ; CurrentYear &"-"& CurrentYear+1 ;

Date (8;1;CurrentYear-1) ≤ _date inscription  ≤ Date (7;31;CurrentYear) ; CurrentYear-1 &"-"& CurrentYear ;

Date (8;1;CurrentYear-2) ≤ _date inscription  ≤ Date (7;31;CurrentYear-1) ; CurrentYear-2 &"-"& CurrentYear-1 ;

Date (8;1;CurrentYear-3) ≤ _date inscription  ≤ Date (7;31;CurrentYear-2) ; CurrentYear-3 &"-"& CurrentYear-2 ;

Date (8;1;CurrentYear-4) ≤ _date inscription  ≤ Date (7;31;CurrentYear-3) ; CurrentYear-4 &"-"& CurrentYear-3 ;

Date (8;1;CurrentYear-5) ≤ _date inscription  ≤ Date (7;31;CurrentYear-4) ; CurrentYear-5 &"-"& CurrentYear-4 ;

)

But the results are always the first line of the case even though it should be 2010-2011 or whatever.

What is it that goes wrong?

Note Current year  = Year ( Get (CurrentDate)) >> Global field

Thank you

• ###### 1. Re: Check if date between two dates

First thing to check is to make sure that _date inscription is of type date and not of type text.

• ###### 2. Re: Check if date between two dates

It is timestamp...

• ###### 3. Re: Check if date between two dates

Then that's the wrong data type for this calculation

Try using Getasdate ( _date inscription )

In each part of this calculation where you now have: _date inscription

• ###### 4. Re: Check if date between two dates
Case (

Date (8;1;CurrentYear) ≤ GetAsDate ( _date inscription )  ≤ Date (7;31;CurrentYear+1) ; CurrentYear &"-"& CurrentYear+1 ;

Date (8;1;CurrentYear-1) ≤ GetAsDate ( _date inscription )  ≤ Date (7;31;CurrentYear) ; CurrentYear-1 &"-"& CurrentYear ;

Date (8;1;CurrentYear-2) ≤ GetAsDate ( _date inscription )  ≤ Date (7;31;CurrentYear-1) ; CurrentYear-2 &"-"& CurrentYear-1 ;

Date (8;1;CurrentYear-3) ≤ GetAsDate ( _date inscription )  ≤ Date (7;31;CurrentYear-2) ; CurrentYear-3 &"-"& CurrentYear-2 ;

Date (8;1;CurrentYear-4) ≤ GetAsDate ( _date inscription )  ≤ Date (7;31;CurrentYear-3) ; CurrentYear-4 &"-"& CurrentYear-3 ;

Date (8;1;CurrentYear-5) ≤ GetAsDate ( _date inscription )  ≤ Date (7;31;CurrentYear-4) ; CurrentYear-5 &"-"& CurrentYear-4 ;

)

Does not work...
• ###### 5. Re: Check if date between two dates

How silly of me to miss a basic syntax error. You need to use And here in each Boolean expression:

Case (

Date (8;1;CurrentYear) ≤ GetAsDate ( _date inscription )  AND
GetAsDate ( _date inscription ) ≤ Date (7;31;CurrentYear+1) ; CurrentYear &"-"& CurrentYear+1 ;

Date (8;1;CurrentYear-1) ≤ GetAsDate ( _date inscription ) AND
GetAsDate ( _date inscription ) ≤ Date (7;31;CurrentYear+1); CurrentYear-1 &"-"& CurrentYear ;

Date (8;1;CurrentYear-2) ≤ GetAsDate ( _date inscription ) AND
GetAsDate ( _date inscription ) ≤ Date (7;31;CurrentYear+1); CurrentYear-2 &"-"& CurrentYear-1 ;

Date (8;1;CurrentYear-3) ≤ GetAsDate ( _date inscription ) AND
GetAsDate ( _date inscription ) ≤ Date (7;31;CurrentYear+1); CurrentYear-3 &"-"& CurrentYear-2 ;

Date (8;1;CurrentYear-4) ≤ GetAsDate ( _date inscription ) AND
GetAsDate ( _date inscription ) ≤ Date (7;31;CurrentYear+1); CurrentYear-4 &"-"& CurrentYear-3 ;

Date (8;1;CurrentYear-5) ≤ GetAsDate ( _date inscription )  AND
GetAsDate ( _date inscription ) ≤ Date (7;31;CurrentYear+1); CurrentYear-5 &"-"& CurrentYear-4 ;

)
• ###### 6. Re: Check if date between two dates

You got it right... again!

You help me every time, you are amazing :)

Thank you