FormatTimestamp ( TheTimestamp ; FormatString

Uses the flags available in the Unix date tool to format a timestamp.

Average rating: 4.3 (40 votes) Log in to vote

Chuck Ross - Show more from this author
Chivalry Software
http://chivalrysoftware.com

Sample input:

FormatTimestamp( Get( CurrentTimeStamp ); "%a, %b %d, %Y" )

Sample output:

Thu, Oct 21, 2010

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// Uses the same flags as the Unix date command tool to take a date and format it using te
//   provided format string. Includes the following format options.

Let(
  [
    $_Timestamp = GetAsTimestamp( TheTimestamp );
    $_Month = Month( $_Timestamp );
    $_Day = Day( $_Timestamp );
    $_Year = Year( $_Timestamp );
    $_Hour = Hour( $_Timestamp );
    $_LowHour = Mod( $_Hour; 12 );
    $_Minute = Minute( $_Timestamp );
    $_Second = Seconds( $_Timestamp );

//  D - Date in mm/dd/yy format (06/14/06)
    $_D = Right( "0" & $_Month; 2 ) & "/" &
      Right( "0" & $_Day; 2 ) & "/" &
      Right( $_Year; 2 );
//  x - Date in standard format for locale, for this solution just the same as D
    $_xx = $_D;

//  C - Century (20 for 2006)
    $_C = Left( $_Year; 2 );
//  Y - Year in 4-digit format (2006)
    $_Y = $_Year;
//  y - Year in 2-digit format (06)
    $_yy = Right( $_Year; 2 );
//  G - Same as Y
    $_G = $_Y;
//  g - Same as y
    $_g = $_yy;

//  b - Month name - abbreviated (Jan)
    $_bb = Left( MonthName( $_Timestamp ); 3 );
//  B - Month name - full (January)
    $_B = MonthName( $_Timestamp );
//  h - Same as b
    $_hh = $_bb;
//  m - Month number (06)
    $_mm = Right( "0" & $_Month; 2 );

//  W - Week of the year (00-52)
    $_W = WeekOfYear( $_Timestamp ) - 1;
//  V - Week of the year (01-51)
    $_V = $_W + 1;
//  U - Same as W
    $_U = $_W;

//  A - Day of the week - full name (Monday)
    $_A = DayName( $_Timestamp );
//  a - Day of the week - abbreviated name (Mon)
    $_aa = Left( $_A; 3 );
//  u - Day of the week - number (Sunday = 1, although date tool has Monday = 1)
    $_uu = DayOfWeek( $_Timestamp );
//  d - Day of the month - 2 digits (05)
    $_dd = Right( "0" & $_Day; 2 );
//  e - Day of the month - digit preceded by a space ( 5)
    $_ee = " " & $_Day;
//  j - Day of the year - (1-366)
    $_jj = DayOfYear( $_Timestamp );
//  w - Same as u
    $_ww = $_uu;

//  p - AM or PM
    $_pp = Case( $_Hour < 12; "AM"; "PM" );
//  r - Time in 12-hour format (09:15:36 AM)
    $_rr = Right( "0" & $_LowHour; 2 ) & ":" &
      Right( "0" & $_Minute; 2 ) & ":" &
      Right( "0" & $_Second; 2 ) & " " &
      $_pp;
//  R - Time in 24-hour format - no seconds (17:45)
    $_R = Right( "0" & $_Hour; 2 ) & ":"  &
      Right( "0" & $_Minute; 2 );
//  T - Time in 24-hour format (17:45:52)
    $_T = $_R & ":" & Right( "0" & $_Second; 2 );
//  X - Same as T
    $_X = $_T;
//  Z - Time zone offset from UTC (-07), NOT SUPPORTED

//  H - Hour in 24-hour format (17)
    $_H = Right( "0" & $_Hour; 2 );
//  I - (upper case i) Hour in 12-hour format (05)
    $_I = Right( "0" & $_LowHour; 2 );
//  k - Same as H
    $_k = $_H;
//  l - (lower case L) Same as I
    $_ll = $_I;

//  M - Minutes (35)
    $_M = Right( "0" & $_Minute; 2 );
//  S - Seconds (05)
    $_S = Right( "0" & $_Second; 2 );
//  s - Seconds elapsed since 1/1/1970
    $_ss = GetAsNumber( $_Timestamp - Timestamp( Date( 1; 1; 1970 ); Time( 0; 0; 0 ) ) )
  ];

  Substitute( FormatString;
    [ "%%"; "%"];
    [ "%D"; $_D ];
    [ "%x"; $_xx ];
    [ "%C"; $_C ];
    [ "%Y"; $_Y ];
    [ "%y"; $_yy ];
    [ "%G"; $_G ];
    [ "%g"; $_gg ];
    [ "%b"; $_bb ];
    [ "%B"; $_B ];
    [ "%h"; $_hh ];
    [ "%m"; $_mm ];
    [ "%W"; $_W ];
    [ "%V"; $_V ];
    [ "%U"; $_U ];
    [ "%a"; $_aa ];
    [ "%A"; $_A ];
    [ "%u"; $_uu ];
    [ "%d"; $_dd ];
    [ "%e"; $_ee ];
    [ "%j"; $_jj ];
    [ "%w"; $_ww ];
    [ "%p"; $_pp ];
    [ "%r"; $_rr ];
    [ "%R"; $_R ];
    [ "%T"; $_T ];
    [ "%X"; $_X ];
    [ "%H"; $_H ];
    [ "%I"; $_I ];
    [ "%k"; $_kk ];
    [ "%l"; $_ll ];
    [ "%M"; $_M ];
    [ "%S"; $_S ];
    [ "%s"; $_ss ]
  )
)

Read the man page for the date tool for the flags that were used. %Z (time zone offset) isn't supported at this time, but if anyone has suggestions for how to do so without plugins or being platform specific, let me know.

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

FormatTimestamp ( TheTimestamp ; FormatString

Comments

Support this website.

Support this website.