5 Replies Latest reply on Jul 29, 2016 8:58 AM by wintertj

    Sortable timestamp, convert FM to DB2 style format custom function

    wintertj

      Don't know if there are too many other DB2 folks, but hopefully this will help somebody some time. I learned SQL on DB2 and its fixed 19 character timestamp format is so ingrained in my brain I write dates and times on paper in this way. The nice thing about it is that you can sort it, and the sort is in chronological order down to the second because it uses 24 hour military time and puts leading zeros in single digit time/date components to make them two digits, and most un-frustratingly to a relative FM newbies, places year first, then month, then date. In other words, a sort ascending will always place a record with a year of 1965 before a year of 1966 or later, regardless of month and date. And its a consistent 19 characters that lines up so nicely with a fixed width font:

       

      YYYY-MM-DD-HH-MM-SS

       

       

      7/28/1972 8:37:54 PM  becomes 1972-07-28-20.37.54

      1/28/2016 11:59:59 PM becomes 2016-01-28-23.59.59

      7/28/2016 12:00 AM    becomes 2016-07-28-00.00.00

      7/28/2016 11:59:59 PM becomes 2016-07-28-23.59.59

      7/28/2022 8:37:11 PM  becomes 2022-07-28-20.37.11

       

      Here's the function where the passed parameter (ts) is a FileMaker timestamp field.

       

      FMtoDB2timestamp (ts) =

       

      Year ( ts )  & "-"  &

      Case (  (Month (ts ) < 10 ) ; "0"  &  Month (ts ) ; Month (ts ) )  & "-"  &

      Case (  (Day (ts ) < 10 ) ; "0"  &  Day (ts ) ; Day (ts ) ) & "-"  &

      Case (  (Hour (ts ) < 10 ) ; "0"  &  Hour (ts ) ; Hour (ts ) ) & "."  &

      Case (  (Minute (ts ) < 10 ) ; "0"  &  Minute (ts ) ; Minute (ts ) ) & "."  &

      Case (  (Seconds (ts ) < 10 ) ; "0"  &  Seconds (ts ) ; Seconds (ts ) )

       

      Just make sure if using it in a calculation field, that the result is set to text.