Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this movie, I am going to introduce you to some of the functions that allow you to manipulate date, time, and timestamp data. The Date, Time, and Timestamp functions are among some of the easier ones to learn because the syntax are really simple. These functions can fall into one of two categories. First of all, they could be functions that are going to extract a bit of information from a date or time, and then there're those that actually construct date, time, or timestamp from bits of information that are going to be provided inside your calculation. Let me show you what those look like. Again, back into the File > Manage > Database, and you see here, we've got a field that's currently DateCalc, but we're going to change that to Calculation.
So we select field on the list, choose a new field type of Calculation, and now hit Change. After FileMaker warns us, we say OK, and here we are again in the Specify Calculation dialog. Take a look at the Date functions up in the upper right-hand corner. You will see we've got a series of them. They all really have a single parameter, except for the construction ones where we can pull YearNames or the fiscal week of the year. You'll see they break into the two categories, if we just look at the date ones here. These are all the functions that will extract a piece of information from a Date field.
This is the field that will construct a date from three different fields. So you see the parameters that we've got here, or if we pull in a value that's supposed to be for the month, one for day, one for year, then that will create a Date field and then of course, we'd want that result to be date ,and then FileMaker will store it and handle it internally as a date value. But the more common date functions are the ones that you see above. So, for example, let's try the one that's called Day. Double-clicking on the function, of course, puts it into our entry area.
Now FileMaker is reminding us, we need to replace the date parameter with a date field. So let's choose InvoiceDate, because we know that that field is the field type of Date. Now, we are going to choose the result, because before we finish our calculations we need to determine what result this output is going to be. In this case, the output is going be a Number, because what we're really looking for is a numeric day. So we choose Calculation result: Number, we hit OK, we hit OK again. Now, on our layout in Browse mode we see we've got the DateCalc, which is right next to the InvoiceDate.
What it's doing is extracting the day from our date. So we see we've got 3/16, we've got 7/7, it gives us a 7, we've got 20 in this case and so on. Now back into Manage > Database. If we manipulate this calculation a bit, instead of giving us a day either 1 through 31, we can choose DayName as another option, InvoiceDate. In this case, our calculations result is going to be Text, because we are looking for the name of the day, which would be Sunday, Monday, Tuesday, and so on.
Hitting OK a couple of times. Now we see that FileMaker is evaluating the fields and determining what day of the week that was. This is a good example of how some of the date functions pair up. You'll see, in the Date functions list, that we've got Day, DayName, Month, MonthName, and we've got WeekOfYear, which gives you a numeric value. Year just pulls a numeric value for the year, and YearName is actually something that's used in Japanese versions of FileMaker.
Now when it comes to Time, see that we've got a calculation already created here called TimeCalc that's currently Text, but let's make that a Calculation field. Hit Change, dismiss the dialog and quickly look at the Time functions. These are very easy. These will either do one of two things: extract a numeric hour, a minute or seconds from a Time field or construct a time with three different parameters, one representing hour, of course, then minutes and seconds. So real quick, what we'll do is we'll pull the Hour. We have a field here called TimeCreated that represents a time value, so we are going to use that one.
Double-click on TimeCreated, and in this case our Calculation result is a Number, which is going to extract the hour from our TimeCreated field. So we'll just keep it a Number, we hit OK, then OK. Now we see for each one of the times, it just gives us a numeric integer that represents the hour in each one of the TimeCreated times. All of these have 10, which represents the time created, 10:42 AM. The Timestamp fields are very similar to these two. They just combine the two components together that we saw in both Date and Time.
So this lesson we just focused on a core number of Date and Time functions, and as you see in the case of Time and Timestamp functions, there's really only a few of them to learn. But in the case of date, these are the ones that are the most useful, so it's best to become adept at using these functions without ever having to need to refer to the functions as a source.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.