Tag: Date

General, Level: Intermediate, Version: FM 8 or later

The Last Day of the Month, part 3

Kevin Frank

Grizzled FileMaker veterans are fond of saying things like, “You ought to know at least three different ways to accomplish any given task.” With that in mind, I hereby submit a third method for calculating the last day of a given month.

Let ( [
theDate = Get ( CurrentDate ) ;
monthNum = Month ( theDate ) ;
yearNum = Year ( theDate ) ;
febLastDay = 28 +
Case (
Mod ( yearNum ; 400 ) = 0 ; 1 ;
Mod ( yearNum ; 100 ) = 0 ; 0 ;
Mod ( yearNum ; 4 ) = 0 ; 1 ;
0
) ;
dayNum = Choose ( monthNum ; "" ; 31 ; febLastDay ; 31 ;
30 ; 31 ; 30 ; 31 ; 31 ; 30 ; 31 ; 30 ; 31
)
] ;

Date ( monthNum ; dayNum ; yearNum )

) // end let

If you read yesterday’s post, you may have noted a resemblance between this calculation and its predecessor, at least as far as the “Let” portion goes. The main difference is the addition of a dayNum variable populated via the Choose() function.

In case you’re not comfortable with Choose(), its format is

Choose (
test ; result if test = 0 { ; result if test = 1 ; result if test = 2... }
)

…where “test” is any non-negative whole number, and the results in braces are optional. At first this function may seem confusing but it turns out to be a very compact replacement for the Case() function, under a strictly defined set of circumstances.

Say, for example, in a table called “test”, you have a field called “score”, which can contain any integer between 0 and 9, and you want convert that value to its corresponding name (“zero,” “one,” “two”, etc.). You could certainly accomplish this with Case() and the statement might look like this:

Case (
test::score = 0 ; "zero" ;
test::score = 1 ; "one" ;
test::score = 2 ; "two" ;
test::score = 3 ; "three" ;
test::score = 4 ; "four" ;
test::score = 5 ; "five" ;
test::score = 6 ; "six" ;
test::score = 7 ; "seven" ;
test::score = 8 ; "eight" ;
test::score = 9 ; "nine"
)

The exact same result can be obtained far more economically thus:

Choose ( test::score ;
"zero" ; "one" ; "two" ; "three" ; "four" ;
"five" ; "six" ; "seven" ; "eight" ; "nine"
)

Essentially, Choose uses test::score as a pointer to the correct “result”, via what’s known as a zero-based index, so a test::score value of 0 corresponds to the first result, a test::score of 1 corresponds to the second result, etc.

In the case of our Last Day of the Month problem, there is no month number of 0, only 1 through 12, so our first result is "" to accommodate the non-existent zero result.

General, Level: Beginner, Version: FM 8 or later

The Last Day of the Month, part 2

Kevin Frank

Yesterday we explored a method to calculate the last day of the month, and by way of introduction I said,

You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years.

This time around let’s look at that method, and those alleged “gymnastics”. They turn out to not be terribly convoluted; in fact, the most time-consuming part of this could be simply defining the rules for what constitutes a leap year.

But first let’s pretend there’s no such thing as a leap year or a leap day. In that case we could write our calculation as follows:

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 ; yearNum ) ;
      <etc.>

Okay, that was a pleasant interlude, but now back to reality. Superficially, it appears that all years evenly divisible by 4 are leap years, but it’s a bit more complicated than that. A year is a leap year when:

  1. it is evenly divisible by 400
    or
  2. it is evenly divisible by 4 but not evenly divisible by 100

It’s important to know all the rules, because we don’t want our database breaking on February 29th in the year 2400, do we?

So how do we determine “even divisibility”? We do so by using the Mod() function, which returns the remainder when you divide one number by another. If the result of the Mod() operation is zero, then we know the first number is evenly divisible by the second.

For example, Mod ( 2000 ; 400 ) = 0, which passes test #1 above, and tells us that the year 2000 was a leap year. The year 1900 cannot pass either of the above tests, which tells us it was not a leap year. Bearing in mind that FileMaker uses calculation “short-circuiting” (stops calculating as soon as one of its logical tests evaluate as true), here’s a calc that returns a 1 for leap years and otherwise returns a zero:

   Let ( yearNum = Year ( Get ( CurrentDate ) ) ;
      Case (
         Mod ( yearNum ; 400 ) = 0 ; 1 ;
         Mod ( yearNum ; 100 ) = 0 ; 0 ;
         Mod ( yearNum ; 4 ) = 0 ; 1 ;
         0
      )
   )   //   end let

Okay, now that we’ve gotten to the heart of the matter, we can put it all together and write our calculation.

   Let ( [
      theDate = Get ( CurrentDate ) ;
      monthName = MonthName ( theDate ) ;
      yearNum = Year ( theDate ) ;
      possibleLeapDay =
         Case (
            Mod ( yearNum ; 400 ) = 0 ; 1 ;
            Mod ( yearNum ; 100 ) = 0 ; 0 ;
            Mod ( yearNum ; 4 ) = 0 ; 1 ;
            0
         )
   ] ;
   Case (
      monthName = "January" ; Date ( 1 ; 31 ; yearNum ) ;
      monthName = "February" ; Date ( 2 ; 28 + possibleLeapDay ; yearNum ) ;
      monthName = "March" ; Date ( 3 ; 31 ; yearNum ) ;
      monthName = "April" ; Date ( 4 ; 30 ; yearNum ) ;
      monthName = "May" ; Date ( 5 ; 31 ; yearNum ) ;
      monthName = "June" ; Date ( 6 ; 30 ; yearNum ) ;
      monthName = "July" ; Date ( 7 ; 31 ; yearNum ) ;
      monthName = "August" ; Date ( 8 ; 31 ; yearNum ) ;
      monthName = "September" ; Date ( 9 ; 30 ; yearNum ) ;
      monthName = "October" ; Date ( 10 ; 31 ; yearNum ) ;
      monthName = "November" ; Date ( 11 ; 30 ; yearNum ) ;
      monthName = "December" ; Date ( 12 ; 31 ; yearNum )
   )  
   )   //   end let

One of the nice things about yesterday’s elegant little calculation was that we didn’t have to think about leap year at all. We let the FileMaker calculation engine worry about that for us. But there’s something very different I like about today’s calculation… it’s supremely readable. It’s not elegant, and it’s not succinct, but it gets the job done. And that’s one of my favorite things about FileMaker: there is rarely only one way to solve a problem.

General, Level: Beginner, Version: FM 8 or later

The Last Day of the Month, part 1

Kevin Frank

This seems an appropriate day to post this. At the risk of stating the obvious, the first day of a given month is a very easy date to calculate, because the day number will always be 1. So, assuming we want to dynamically calculate the date for the first day of the current month (whatever that month may happen to be), we can simply plug the values in as follows:

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) ; 1 ; Year ( x ) )
      ) // end let

Wouldn’t it be nice if it were that easy to calculate the date for the last day of the month? The problem of course, is that the last day of the month can be 28, 29, 30 or 31.

You might be tempted to use a Case() statement, and test each month individually, but then you’d have to engage in some calculation gymnastics to accommodate Feb 29th in leap years. Here’s an approach I like because it’s both succinct and bullet proof.

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) + 1 ; 1 ; Year ( x ) ) - 1
      ) // end let

In a nutshell, this tells FileMaker to calculate the date corresponding to the 1st of next month, and then to subtract 1 day from that. It even works in December, because FileMaker is smart enough to convert Date(13;1;2010) to January 1, 2011, and of course if we subtract one day from that we get December 31, 2010.

And here’s a tip I picked up from Geoff Gerhard at Creative Solutions: the calc can be further simplified, by removing the “-1” from the end, and changing the day number to zero, which FileMaker is smart enough to interpret as “the day before the first of the month”.

      Let ( x = Get(CurrentDate) ;
         Date ( Month ( x ) + 1 ; 0 ; Year ( x ) )
      ) // end let

Incidentally, I’ve used Get(CurrentDate) in these examples, and that’s fine for a single-user system. For a multi-user system, it’s a good idea to instead use…

      GetAsDate ( Get ( CurrentHostTimestamp ) )

…which ensures your users will all be on the same page, or date at any rate, regardless of what date their computer thinks it is. Time can be calculated in a similar manner:

      GetAsTime ( Get ( CurrentHostTimestamp ) )

And the nice thing about this is that if you open the file single-user, the above calcs still work since your computer is considered to be the host. That’s what I call all gain and no pain.