FormatDateTime ( dateValue , timeValue , format )

FormatDateTime formats FileMaker Pro date and time data using PHP Date formatting syntax. Returns a string of the dateValue and/or timeValue as specified in format. If dateValue or timeValue are empty current date or current time are used. Optionally, use the backslash as an escape code to include text not intended to be formatted. This custom function formats the output using PHP Date function format codes but with FileMaker conventions.

Parameters

• dateValue – If empty the current date is used.
• timeValue – If empty the current time is used.
• format – Specifies the content of the returned string. Optionally use the backslash as an escape code to include text not intended to be formatted.

Examples

• FormatDateTime ( 10-31-2011, “”, “D., F j, Y”) = Mon., October 31, 2011
• FormatDateTime ( 10-31-2011, 8:46:21, “Y-m-d His \zulu\”) = 2011-10-31 084621 zulu
• FormatDateTime ( 10-3-2011, “”, “\today is the \jS\ day\”) = today is the 3rd day

HELP

Passing ‘?’ as the only text in format returns this help text with the following code explanations.

Escaped \ is written \\, all other escaped text must be enclosed within an opening \ and a closing \.

Formatting performed using PHP Date function format syntax but with FileMaker Pro conventions.

Day ——-

• d – 01-31
• D – Mon-Sun
• j – 1-31
• l (lowercase ‘L’) – Sunday-Saturday
• N – 1 (Monday) … 7 (Sunday). (PHP convention: ISO-8601 representation of day of week.)
• S – st, nd, rd or th (English ordinal suffix.)
• w – 1 (Sunday) … 7 (Saturday). (PHP convention : 0 (for Sunday) through 6 (for Saturday).)
• z – 1-366. (PHP convention: 0-365.)

Week —–

• W – 1-54, week of year Jan. 1. (PHP convention: ISO-8601 week number of year, weeks starting on Monday.)

Month —-

• F – January-December
• m – 01-12
• M – Jan-Dec
• n – 1-12
• t – 28-31

Year —–

• L – Leap year (1/0)
• o – year number. ISO-8601 year number.
• Y – 4 digit year
• y – 2 digit year

Time —-

• a – am/pm
• A – AM/PM
• g – Hour 1-12
• G – Hour 0-23
• h – Hour 01-12
• H – Hour 00-23
• i – Min 00-59
• s – Sec 00-59
• u – Microseconds

NOTICE: This function is a recursive function. If you change the name of it, then change the name references inside the code.

FormatDateTime code: Copy the selected code below (click on Select All to highlight all the code) to the clipboard and paste it into a newly created Custom Function. Download the code in a text file from here.

<!–raw–>Let( [d = If ( IsEmpty( dateValue ); Get ( CurrentDate ); GetAsDate ( dateValue ) );<br /><br /> t = If ( IsEmpty( timeValue ); Get ( CurrentTime ); GetAsTime ( timeValue ) );<br /><br /> format = format & If ( Mod ( PatternCount ( format; “\\” ); 2 ); “\\”; “” ); // Append \ if count not even.<br /><br /> f = Left ( format; 1 );<br /><br /> f = If ( f = “\\”; Middle ( format; 1; Position ( format; “\\”; 2; 1 ) ); f ); // Escape found, set to full escaped string.<br /><br /> c = Case(<br /><br /> f = “\\\\”; “\\”; // If a single blackslash has been escaped, make f a single backslash.<br /><br /> Length ( f ) > 1; Middle ( f; 2; Position ( f; “\\”; 2; 1 ) – 2); // Get the contents.<br /><br /> Exact ( f; “d” ) ; Right ( GetAsText ( 100 + Day ( d ) ); 2 ) ;<br /><br /> Exact ( f; “D” ) ; Left ( DayName ( d ) ; 3 ) ;<br /><br /> Exact ( f; “j” ) ; GetAsText ( Day ( d ) ) ;<br /><br /> Exact ( f; “l” ) ; DayName ( d ) ;<br /><br /> Exact ( f; “N” ) ; GetAsText ( If ( DayOfWeek ( d ) – 1 = 0; 7; DayOfWeek ( d ) – 1 ) ) ;<br /><br /> Exact ( f; “S” ) ; If ( PatternCount ( “11 12 13”; Right ( GetAsText ( 100 + Day ( d ) ); 2 ) ); “th”; Choose ( Mod ( Day ( d ) ; 10 ) ; “th”; “st”; “nd”; “rd”; “th”; “th”; “th”; “th”; “th”; “th” ) );<br /><br /> Exact ( f; “w” ) ; GetAsText ( DayOfWeek ( d ) ) ;<br /><br /> Exact ( f; “z” ) ; GetAsText ( DayOfYear ( d ) ) ;<br /><br /> Exact ( f; “W” ) ; GetAsText ( WeekOfYear ( d ) ) ;<br /><br /> Exact ( f; “F” ) ; MonthName ( d ) ;<br /><br /> Exact ( f; “m” ) ; Right ( GetAsText ( 100 + Month ( d ) ); 2 ) ;<br /><br /> Exact ( f; “M” ) ; Left ( MonthName ( d ) ; 3 ) ;<br /><br /> Exact ( f; “n” ) ; GetAsText ( Month ( d ) ) ;<br /><br /> Exact ( f; “t” ) ; GetAsText ( Day ( Date ( Month ( d ) + 1; 1; Year ( d ) ) – 1 ) ) ;<br /><br /> Exact ( f; “L” ) ; GetAsText ( If ( Day ( Date ( 3; 1; Year ( d ) ) – 1 ) = 29; 1; 0 ) ) ;<br /><br /> Exact ( f; “o” ) ; GetAsText ( Year ( d ) + Case ( WeekOfYear ( d ) = 1 and DayOfWeek ( Date ( 1; 1; Year ( d ) ) ) > 1; -1; WeekOfYear ( d ) > 52 and DayOfWeek ( Date ( 12; 31; Year ( d ) ) ) < 7 ; 1 ; 0 ) ) ;<br /> Exact ( f; “Y” ) ; GetAsText ( Year ( d ) ) ;<br /> Exact ( f; “y” ) ; Right ( GetAsText ( Year ( d ) ) ; 2 ) ;<br /> Exact ( f; “a” ) ; If ( GetAsNumber ( t ) > 43200; “pm”; “am” ) ;<br /><br /> Exact ( f; “A” ) ; If ( GetAsNumber ( t ) > 43200; “PM”; “AM” ) ;<br /><br /> Exact ( f; “g” ) ; GetAsText ( If ( Hour ( t ) > 12; Hour ( t ) – 12; Hour ( t ) ) ) ;<br /><br /> Exact ( f; “G” ) ; GetAsText ( Hour ( t ) ) ;<br /><br /> Exact ( f; “h” ) ; Right ( GetAsText ( 100 + If ( Hour ( t ) > 12; Hour ( t ) – 12; Hour ( t ) ) ); 2) ;<br /><br /> Exact ( f; “H” ) ; Right ( GetAsText ( 100 + Hour ( t ) ); 2 );<br /><br /> Exact ( f; “i” ) ; Right ( GetAsText ( 100 + Minute ( t ) ); 2);<br /><br /> Exact ( f; “s” ) ; Right ( GetAsText ( 100 + Seconds ( t ) ); 2);<br /><br /> Exact ( f; “u” ) ; GetAsText ( GetAsNumber ( t ) ) ;<br /><br /> f);<br /><br /> // Prepare for recursive call.<br /><br /> format = Right ( format; Length (format ) – Length ( f ) );<br /><br /> // Prepare help text.<br /><br /> h = If ( f = “?” and Length ( format ) = 0; “FormatDateTime ( dateValue, timeValue, format )¶¶Returns the dateValue and timeValue formatted as specified in format.¶¶If dateValue or timeValue are empty current date or current time are used.¶Optionally use the backslash as an escape code to include text not intended to be formatted.¶Escaped \ is written \\\, all other escaped text must be enclosed within an opening \ and a closing \.¶Format codes are the same as PHP date() function format codes, but with Filemaker conventions.¶¶<br /><br /> Day codes:¶<br /><br /> d – 01-31¶<br /><br /> D – Mon-Sun¶<br /><br /> j – 1-31¶<br /><br /> l – (lowercase ‘L’) Sunday-Saturday¶<br /><br /> N – 1 (Monday) … 7 (Sunday). (PHP convention: ISO-8601 representation of day of week.)¶<br /><br /> S – st, nd, rd or th (English ordinal suffix.)¶<br /><br /> w – 1 (Sunday) … 7 (Saturday). (PHP convention : 0 (Sunday) … 6 (Saturday).)¶<br /><br /> z – 1-366. (PHP convention: 0-365.)¶¶<br /><br /> Week codes:¶<br /><br /> W – 1-54, week of year Jan. 1. (PHP convention: ISO-8601 week number of year, weeks starting on Monday.)¶¶<br /><br /> Month codes:¶<br /><br /> F – January-December¶<br /><br /> m – 01-12¶<br /><br /> M – Jan-Dec¶<br /><br /> n – 1-12¶<br /><br /> t – 28-31¶¶<br /><br /> Year codes:¶<br /><br /> L – Leap year (1/0)¶<br /><br /> o – year number. ISO-8601 year number.¶<br /><br /> Y – 4 digit year¶<br /><br /> y – 2 digit year¶¶<br /><br /> Time codes:¶<br /><br /> a – am/pm¶<br /><br /> A – AM/PM¶<br /><br /> g – Hour: 1-12¶<br /><br /> G – Hour: 0-23¶<br /><br /> h – Hour: 01-12¶<br /><br /> H – Hour: 00-23¶<br /><br /> i – Min: 00-59¶<br /><br /> s – Sec: 00-59¶<br /><br /> u – Microseconds¶¶<br /><br /> Examples:¶<br /><br /> FormatDateTime ( 9/21/2005, 11:06:53, \”Y-m-d His \zulu\\\”) = 2005-09-21 110653 zulu¶<br /><br /> FormatDateTime ( 10-3-2011, \”\”, \”\today is the \jS\ day\\\”) = today is the 3rd day<br /><br /> “; “” );<br /><br /> // Recursive call if not empty.<br /><br /> c = c & If ( IsEmpty ( format ); “”; FormatDateTime ( d; t; format ) )];</p><br /> <p>If ( c = “?”; h; c ) // Send back just help text if format was a question mark (?).<br /><br /> //c</p><br /> <p>)</p><br /> <p>/*<br /><br /> FormatDateTime ( dateValue, timeValue, format )</p><br /> <p>by Lewis C. Lorenz</p><br /> <p>1-1-14 Updated</p><br /> <p>NOTICE: This is a recursive function. If you change the function name then also change it in the function code.</p><br /> <p>Returns the dateValue or timeValue formatted as specified in format.<br /><br /> If dateValue or timeValue are empty current date or current time are used.<br /><br /> Optionally use the backslash as an escape code to include text not intended to be formatted.<br /><br /> Escaped \ is written \\, all other escaped text must be enclosed within an opening \ and a closing \.<br /><br /> Formatting performed using PHP date() function format codes but with Filemaker conventions.</p><br /> <p>Passing ‘?’ as the whole value of format returns this help text and the following code explanations.</p><br /> <p>Day ——-<br /><br /> d – 01-31<br /><br /> D – Mon-Sun<br /><br /> j – 1-31<br /><br /> l (lowercase ‘L’) – Sunday-Saturday<br /><br /> N – 1 (Monday) … 7 (Sunday). (PHP convention: ISO-8601 representation of day of week.)<br /><br /> S – st, nd, rd or th (English ordinal suffix.)<br /><br /> w – 1 (Sunday) … 7 (Saturday). (PHP convention : 0 (for Sunday) through 6 (for Saturday).)<br /><br /> z – 1-366. (PHP convention: 0-365.)<br /><br /> Week —–<br /><br /> W – 1-54, week of year Jan. 1. (PHP convention: ISO-8601 week number of year, weeks starting on Monday.)<br /><br /> Month —-<br /><br /> F – January-December<br /><br /> m – 01-12<br /><br /> M – Jan-Dec<br /><br /> n – 1-12<br /><br /> t – 28-31<br /><br /> Year —–<br /><br /> L – Leap year (1/0)<br /><br /> o – year number. ISO-8601 year number.<br /><br /> Y – 4 digit year<br /><br /> y – 2 digit year<br /><br /> Time —-<br /><br /> a – am/pm<br /><br /> A – AM/PM<br /><br /> g – Hour 1-12<br /><br /> G – Hour 0-23<br /><br /> h – Hour 01-12<br /><br /> H – Hour 00-23<br /><br /> i – Min 00-59<br /><br /> s – Sec 00-59<br /><br /> u – Microseconds</p><br /> <p>Examples:<br /><br /> FormatDateTime ( 9/21/2005, 11:06:53, “Y-m-d His \zulu\”) = 2005-09-21 110653 zulu<br /><br /> FormatDateTime ( 10-3-2011, “”, “\today is the \jS\ day\”) = today is the 3rd day<br /><br /> */<!–/raw–>