Abstract
These three categories provide functions that manipulate date and time values.
Access provided by CONRICYT-eBooks. Download chapter PDF
Similar content being viewed by others
These three categories provide functions that manipulate date and time values.
Date Function Reference
Date functions are used to create and manipulate dates. FileMaker provides the following built-in date functions:
-
Date
-
Day
-
Day Name
-
DayNameJ
-
DayOfWeek
-
DayOfYear
-
Month
-
MonthName
-
MonthNameJ
-
WeekOfYear
-
WeekOfYearFiscal
-
Year
-
YearName
Date
The Date function creates a date object from separate numeric month, day, and year values.
Format | Date ( month ; day ; year ) |
Parameters | month = a number indicating a month. day = a number indicating a day of the month. year = a number indicating a year. |
Result | A date object constructed from the values provided. |
This basic example constructs a date object from three numeric values:
Date ( 1 ; 15 ; 2017 ) // result = 1/15/2017
This example demonstrates that any of the parameters can be expressions that will be evaluated prior to the construction of the date:
Date ( 1 ; 15 ; 2027 - 10 ) // result = 1/15/2017
The function will automatically wrap to a new month, day or year if the month or day provided falls out of range. For example, a month value of 13 will cause the function to automatically return a date for January of the following year:
Date ( 13 ; 15 ; 2017 ) // result = 1/15/2018
Day
The Day function extracts the day number of the month for a given date.
Format | Day ( date ) |
Parameters | date = a date. |
Result | A number. |
These examples show the function operating on different input:
Day ( Date ( 1 ; 15 ; 2017 ) ) // result = 15 Day ( "2/26/2017" ) // result = 26
DayName
The DayName function calculates the name of the weekday for a given date.
Format | DayName ( date ) |
Parameters | date = a date. |
Result | A text string containing the name of the weekday for the date provided. |
These examples show the function operating on different input:
DayName ( Date ( 1 ; 15 ; 2017 ) ) // result = Sunday DayName ( "6/27/1758" ) // result = Tuesday
DayNameJ
The DayNameJ function performs the same function as DayName but returns text in Japanese. For more information on Japanese functions, see FileMaker’s documentation.
DayOfWeek
The DayOfWeek function calculates a number representing the day of the week for a given date.
Format | DayOfWeek ( date ) |
Parameters | date = a date. |
Result | A number from 1 to 7 indicating where the date provided falls on the calendar week from Sunday to Monday. |
These examples show the function operating on different input:
DayOfWeek ( Date ( 1 ; 15 ; 2017 ) ) // result = 1 DayOfWeek ( "6/27/1758" ) // result = 3
DayOfYear
The DayOfYear function calculates a number representing the day of the year for a given date.
Format | DayOfYear ( date ) |
Parameters | date = a date. |
Result | A number from 1 to 365 indicating where the date provided falls within a year. |
These examples show the function operating on different input:
DayOfYear ( Date ( 1 ; 15 ; 2017 ) ) // result = 15 DayOfYear ( "6/27/1758" ) // result = 178
Month
The Month function calculates the number of the month for a given date.
Format | Month ( date ) |
Parameters | date = a date. |
Result | A number from 1 to 12 indicating the month number of the date provided. |
These examples show the function operating on different input:
Month ( Date ( 1 ; 15 ; 2017 ) ) // result = 1 Month ( "6/27/1758" ) // result = 6
MonthName
The MonthName function calculates the name of the month for a given date.
Format | MonthName ( date ) |
Parameters | date = a date. |
Result | A text string containing the name of the month of the date provided. |
These examples show the function operating on different input:
MonthName ( Date ( 1 ; 15 ; 2017 ) ) // result = "January" MonthName ( "6/27/1758" ) // result = "June"
MonthNameJ
The MonthNameJ function performs the same function as MonthName but returns text in Japanese. For more information on Japanese functions, see FileMaker’s documentation.
WeekOfYear
The WeekOfYear function calculates a number representing the week of the year for a given date.
Format | WeekOfYear ( date ) |
Parameters | date = a date. |
Result | A number from 1 to 52 indicating the week number within a year of the date provided. |
These examples show the function operating on different input:
WeekOfYear ( Date ( 1 ; 15 ; 2017 ) ) // result = 3 WeekOfYear ( "6/27/1758" ) // result = 26
WeekOfYearFiscal
The WeekOfYearFiscal function calculates a number representing the week of a year for a given date based on a specified starting date for a workweek. This is sometimes useful in accounting to calculate if a year has an extra pay period because a week is split across the calendar year boundary.
Format | WeekOfYearFiscal ( date ; startingDay ) |
Parameters | date = a date. startingDay = a number indicating the start of a workweek: 1 = Sunday, 2 = Monday, 3 = Tuesday, etc. |
Result | A number indicating the week of the year for the date provided based on the start of the workweek specified. |
These examples show how January 2, 2009, which falls on a Friday, can be either the first week of 2009 or the fifty-third week of 2008 depending on the day considered to be the start of the week:
WeekOfYearFiscal ( "1/2/2009" ; 1 ) // result = 53 WeekOfYearFiscal ( "1/2/2009" ; 2 ) // result = 1 WeekOfYearFiscal ( "1/2/2009" ; 3 ) // result = 1 WeekOfYearFiscal ( "1/2/2009" ; 4 ) // result = 1 WeekOfYearFiscal ( "1/2/2009" ; 5 ) // result = 1 WeekOfYearFiscal ( "1/2/2009" ; 6 ) // result = 1 WeekOfYearFiscal ( "1/2/2009" ; 7 ) // result = 53
Year
The Year function returns the year for a given date.
Format | Year ( date ) |
Parameters | date = a date. |
Result | A number that is the year for the date provided. |
These examples show the function operating on different input:
Year ( Date ( 1 ; 15 ; 2017 ) ) // result = 2017 Year ( "6/27/1758" ) // result = 1758
YearName
The YearName function calculates the Japanese year name of a given date. For more information on Japanese functions, see FileMaker’s documentation.
Time Function Reference
Time functions are used to create and manipulate time values. FileMaker provides the following built-in time functions:
-
Hour
-
Minute
-
Second
-
Time
Hour
The Hour function extracts the number of hours of a given time.
Format | Hour ( time ) |
Parameters | time = a time. |
Result | The hour number of the time provided. |
These examples show the function operating on different input:
Hour ( "09:15:55 AM" ) // result = 9 Hour ( "4/20/2017 03:30:00 PM" ) // result = 15
Minute
The Minute function extracts the number of minutes from a given time.
Format | Minute ( time ) |
Parameters | time = a time. |
Result | The number of minutes from the time provided. |
These examples show the function operating on different input:
Minute ( "09:15:55 AM" ) // result = 15 Minute ( "4/20/2017 03:30:00 PM" ) // result = 30
Seconds
The Seconds function extracts the number of seconds from a given time.
Format | Seconds ( time ) |
Parameters | time = a time. |
Result | The number of seconds from the time provided. |
These examples show the function operating on different input:
Seconds ( "09:15:55 AM" ) // result = 55 Seconds ( "4/20/2017 03:30:00 PM" ) // result = 0
Time
The Time function creates a time object from separate numeric hours, minutes, and seconds values.
Format | Time ( hours ; minutes ; seconds ) |
Parameters | hours = a number representing hours. minutes = a number representing minutes. seconds = a number representing seconds. |
Result | A time object constructed from the values provided. |
These examples show the function operating on different input:
Time ( 9 ; 15 ; 55 ) // result = 9:15:55 Time ( 2 ; 8 ; 19 ) // result = 2:8:19
Timestamp Function Reference
Timestamp functions are used to create and manipulate timestamp data, which is a combination of a date and time value in a single string. FileMaker provides the following built-in timestamp function:
-
Timestamp
Timestamp
The Timestamp function creates a timestamp based on a given date and time.
Format | Timestamp ( date ; time ) |
Parameters | date = a date. time = a time. |
Result | A timestamp from the date and time provided. |
These examples show that timestamps will automatically add the appropriate AM/PM suffix to the time portion:
Timestamp ( "1/15/2017" ; "9:15:55" ) // result = 1/15/2017 9:15:55 AM Timestamp ( Date ( 5 ; 10 ; 1990 ) ; Time ( 10 ; 30 ; 00 ) ) // result = 5/10/1990 10:30:00 AM
If the hours are out of range, as in military time, the function will automatically convert to civilian time, also with the appropriate AM/PM suffix:
Timestamp ( "1/15/2017" ; "15:15:55" ) // result = 1/15/2017 3:15:55 PM
Summary
In this chapter, we explored all the built-in functions for manipulating date, time, and timestamp values.
Author information
Authors and Affiliations
Rights and permissions
Copyright information
© 2017 Mark Conway Munro
About this chapter
Cite this chapter
Munro, M.C. (2017). Date, Time, and Timestamp Functions. In: Learn FileMaker Pro 16. Apress, Berkeley, CA. https://doi.org/10.1007/978-1-4842-2863-0_17
Download citation
DOI: https://doi.org/10.1007/978-1-4842-2863-0_17
Published:
Publisher Name: Apress, Berkeley, CA
Print ISBN: 978-1-4842-2862-3
Online ISBN: 978-1-4842-2863-0
eBook Packages: Professional and Applied ComputingApress Access BooksProfessional and Applied Computing (R0)