Date Calculation (no longer working)

[Steve Martino]

Hello forum.  I have the following calculation that always seems to be evaluating to false.  It's worked fine for about 2 years, now the script returns 'no records found'.  Using the script debugger, and putting this script step/calculation into the data view, always returns the false parameter:

 

If ( 
 Get ( CurrentDate )  < "8/31/"  & Year ( Get ( CurrentDate ) ) ;
 Get ( CurrentDate )  ;
 "8/31/"  & ( Year ( Get ( CurrentDate ) ) - 1 )
)

 

Basically it's going to create a variable for a global end date used in a search.  This script step for the global start date works fine:

 

If ( 
 Get ( CurrentDate )  < "9/1/"  & Year ( Get ( CurrentDate ) ) ;
 "9/1/"  & Year ( Get ( CurrentDate ) ) - 1 ;
 "9/1/"  & ( Year ( Get ( CurrentDate ) ) - 2 )
)

 

Basically, and probably obviously, for todays date my 2 results should be:

8/4/2014 for gEndDate

9/1/2013 for gStartDate

 

but instead I get:

8/31/2013 for gEndDate

9/1/2013 for gStartDate

 

Didn't change anything, and even though it worked 2 days ago, it doesn't work on any backups.  No luck with recovery either. I did notice that this part is evaluating to '0':

      Get ( CurrentDate )  < "8/31/"  & Year ( Get ( CurrentDate ) )

but in the data viewer "8/31/"  & Year ( Get ( CurrentDate ) ) works (evaluates to 8/31/2014).

 

Any thoughts/suggestions would be greatly appreciated.

Thanks

Steve

[Josh Ormond]

This is going to evaluate to 8/31/2013 as it's written.

 

"8/31/" & ( Year ( Get ( CurrentDate ) ) - 1 ) reads as

 

8 = August

31 = day 31

Year of the current date - 1

 

If ( 

 Get ( CurrentDate )  < "8/31/"  & Year ( Get ( CurrentDate ) ) ;

 Get ( CurrentDate )  ;

 "8/31/"  & ( Year ( Get ( CurrentDate ) ) - 1 )

)

[Steve Martino]

Thanks for your response.  It doesn't evaluate to Get (CurrentDate)?

I thought true would be:

     Get(CurrentDate)

and false would be:

     "8/31/"  & ( Year ( Get ( CurrentDate ) ) - 1 )

hmmmmmm...don't know why it worked all this time and not now.

[comment]

even though it worked 2 days ago

 

It didn't, it's an optical illusion:

"8/31/"  & Year ( Get ( CurrentDate ) )

returns text, not date. The comparison:

Get ( CurrentDate )  < "8/31/"  & Year ( Get ( CurrentDate ) ) 

is comparing two text strings in alphabetical order. In order to compare them as dates, you need to do:

Get ( CurrentDate )  <  GetAsDate ( "8/31/"  & Year ( Get ( CurrentDate ) ) )

or, much more preferably (because it doesn't depend on the file's date format in use):

Get ( CurrentDate )  <  Date ( 8 ; 31 ; Year ( Get ( CurrentDate ) ) )

Similarly, use:

Date ( 8 ; 31 ; Year ( Get ( CurrentDate ) ) - 1 )

instead of:

"8/31/"  & ( Year ( Get ( CurrentDate ) ) - 1 )

[Steve Martino]

Well thanks Comment for that detailed answer.

[eos]

Any thoughts/suggestions would be greatly appreciated.

 

My suggestion would be to use the Date() function to build propers dates for comparison, and use Let() to increase readability; namely …

Let (
  cd = Get ( CurrentDate ) ;
  Case ( 
   cd < Date ( 8 ; 31 ; Year ( cd ) ) ;
   cd ;
   Date ( 8 ; 31 ; Year ( cd ) - 1 )
) )

Let ( [
  cd = Get ( CurrentDate ) ;
  offset = Case ( cd < Date ( 9 ; 1 ; Year ( cd ) ) ; 1 ; 2 )
  ] ; 
  Date ( 9 ; 1 ; Year ( cd ) - offset )
)

[Steve Martino]

Good idea also Oliver.  Who can turn down a Let function?

BTW, are you fully recovered from your World Cup victory? (You could've saved a few goals from the semi finals for the finals )

[Josh Ormond]

Show off!!  

 

Thanks. I was just editing it to add the Date function reference...then got called away.

 

Edit: And thanks for all the years of helping with date calcs like this...I have personally learned a ton from your comments...

 

 

It didn't, it's an optical illusion:

"8/31/"  & Year ( Get ( CurrentDate ) )

returns text, not date. The comparison:

Get ( CurrentDate )  < "8/31/"  & Year ( Get ( CurrentDate ) ) 

is comparing two text strings in alphabetical order. In order to compare them as dates, you need to do:

Get ( CurrentDate )  <  GetAsDate ( "8/31/"  & Year ( Get ( CurrentDate ) ) )

or, much more preferably (because it doesn't depend on the file's date format in use):

Get ( CurrentDate )  <  Date ( 8 ; 31 ; Year ( Get ( CurrentDate ) ) )

Similarly, use:

Date ( 8 ; 31 ; Year ( Get ( CurrentDate ) ) - 1 )

instead of:

"8/31/"  & ( Year ( Get ( CurrentDate ) ) - 1 )

[eos]

(You could've saved a few goals from the semi finals for the finals )

 

Well, it worked     … of course, that semi result would have been the bitter-sweetest of all times if it hadn't!