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

    Check if date between two dates

    Emmanuel

      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
          philmodjunk

               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
            Emmanuel

                 It is timestamp...

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

                   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
                Emmanuel
                     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
                  philmodjunk

                       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
                    Emmanuel

                         You got it right... again!

                          

                         You help me every time, you are amazing :)

                          

                         Thank you