Database Reference
In-Depth Information
NOTE
For time-of-day values, the time function doesn't let you specify a.m. or p.m., so you have to
use 24-hour notation and format the field on the layout to display the time using 12-hour nota-
tion.
▪ The
Timestamp()
function takes just two parameters: Date and Time. It combines the
two into a single timestamp value. It shows June 10, 2015, at 8:30 p.m. like this:
Timestamp ( Date ( 6 ; 10 ; 2015 ) ; Time ( 20 ; 30 ; 0 ) )
The Secret Powers of Date()
Although FileMaker doesn't look at calendars the way people do, that's not all bad. You see
a calendar in absolute terms: April 30 belongs to April, May 1 belongs to May, and that's
that. FileMaker, however, thinks of dates in relative terms and sees no such limitations. You
can use this flexibility to your advantage in calculations—big time. You can give seemingly
illogical parameters to the
Date()
function and have FileMaker produce a valid date anyway.
For example, this calculation actually produces a valid date:
Date ( 7 ; 0 ; 2015 )
You might expect a nonsense result—July 0, 2015. But FileMaker looks at the same code
and says, “No problem. Zero comes before 1, so you must mean the day that comes before
July 1.” And so it returns June 30, 2015.
These same smarts apply to the month as well:
Date ( 15 ; 11 ; 2015 )
That calculation produces March 11,
2016
. In other words, 3 months into the next year, since
15 is 3 months more than 1 year.
This behavior comes in super handy when you're trying to fiddle with dates in calculations.
Suppose you have order records, each one with an order date. You bill on the last day of the
month in which the order was placed, so your calculation needs to figure out that date, which
could be 28, 30, or 31, depending on the month, or even 29 if it's February in a leap year.
That calculation would take an entire page in this topic. But here's a much easier approach:
Instead of calculating which day each month ends, use the fact that the
last
day of
this
month
is always the day
before
the
first
day of
next
month. To start with, you can calculate next
month like this:
Month ( Order Date ) + 1
