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.