formatDate() Function Update

[Bob Sneidar]

Not to be confused with the built-in dateFormat() function, I wrote a special formatDate() function a few years back because I had a need to get a given date in unsupported formats. Particularly I needed an SQL Date format in the form of yyyy-mm-dd. 

I have been frustrated also that there is no built-in function to return a date in the mm/dd/yyyy format, so I just updated my function to include that form using “standard date” as the format parameter. (I can’t think what else to call it. "Y2K Date” seems cheesy.) 

Here is the code if anyone is interested. I should also submit it to the Master Library. I will have to grok how to do that again. 

Bob S



FUNCTION formatDate theDate, theFormat
   /*
   Accepts any valid date for the first parameter. If not a valid date, it simply returns
   what was passed. Second parameter can be any of the following:
   sql date: date in the yyyy-mm-dd format
   short date, abbreviated date, internet date, long date: LC versions of the same
   julian date: Julian number based on (I believe) Jacques formula
   standard date: The date in the form of theFormat
   */
   
   put theDate into tSavedDate
   put the itemdelimiter into theOldDelim
   set the itemdelimiter to "-"
   
   IF the length of item 1 of theDate = 4 AND \
         the number of items of theDate = 3 AND \
         item 1 of theDate is a number AND \
         item 2 of theDate is a number AND \
         item 3 of theDate is a number THEN
      put item 2 of theDate & "/" & \
            item 3 of theDate & "/" & \
            item 1 of theDate into theDate
   END IF
   
   -- replace "." with "/" in theDate
   convert theDate to dateitems
   set the itemdelimiter to theOldDelim
   
   if the number of items of theDate <> 7 then
      answer "'" & theDate & "' is not a valid date format!"
      return tSavedDate
   end if
   
   SWITCH theFormat
      CASE "sql date"
         /*
         put item 1 of theDate & "-" & \
               format("%02d",item 2 of theDate) & "-" & \
               format("%02d",item 3 of theDate) into theDate
         */
         put format("%s-%02d-%02d", item 1 of theDate, item 2 of theDate, \
               item 3 of theDate) into theDate
         break
      CASE "short date"
         convert theDate from dateitems to short date
         break
      CASE "abbreviated date"
         convert theDate from dateitems to abbreviated date
         break
      CASE "abbr date"
         convert theDate from dateitems to abbreviated date
         break
      CASE "internet date"
         convert theDate from dateitems to internet date
         break
      CASE "long date"
         convert theDate from dateitems to long date
         break
      CASE "julian date"
         put the date into theDate
         convert theDate to dateItems
         IF  ((item 2 of theDate = 1) OR (item 2 of theDate = 2)) THEN
            put 1 into theDay
         ELSE
            put 0 into theDay
         END IF
         put item 1 of theDate + 4800 - theDay into theYear
         put item 2 of theDate + (12 * theDay) - 3 into theMonth
         put item 3 of theDate + \
               ((153 * theMonth + 2) div 5) + \
               (365 * theYear) + \
               (theYear div 4) - \
               (theYear div 100) + \
               (theYear div 400) - \
               32045 into theDate
         break
      case "standard date"
         put format("%02d/%02d/%04d", item 2 of theDate, item 3 of theDate, \
               item 1 of theDate) into theDate
         break
      default 
         Answer info “‘“ & theFormat & “‘ is not a validate parameter.” As sheet
         put tSavedDate into theDate
   END SWITCH
   
   return theDate
END formatDate