Formatting a date field properly

Moderators: Moderators, Tech Support

Post Reply
chiebert
Posts: 2
Joined: Wed Apr 08, 2015 1:44 pm

Formatting a date field properly

Post by chiebert »

I'm building a custom Events report to help the office staff insert a 'events this week' section in our leaflet. I'm having difficulty figuring out how to format the date field in the report writer. I'm getting the grouping/rows that I want, but the formatting of the fields themselves is stumping me. Here's what I've got so far:
  • Grouping on DAY(Qmf_temp.evstart)
  • In the Group Header, I'm currently showing MDY(Qmf_temp.evstart) - currently this is almost what I want, though I'd like just MD, leaving off the year
  • In the Detail band, I WANT to show (along with the other fields I'm listing) the h:mm am/pm portion of Qmf_temp.evstart
In PHP and other programming languages, I've got the ability to generate just about any format from an underlying date field. How do I get the same flexibility here? In short, I'm looking for expressions that give me:
  • day Month
  • hh:mm a/pm

deckhouse15
Posts: 2
Joined: Tue May 27, 2014 12:08 pm

Re: Formatting a date field properly

Post by deckhouse15 »

Okay, I'm slogging through this one (unhappily), and for those who follow after, here's how I'm managing it:

[Note: in the following, the "Qmf_temp.evstart" portion is what is inserted when you double-click on the field name in the expression builder - it'll be different for you, depending on what sort of field you're trying to format]

For "Dayname, Monthname dd" [e.g., "Sunday, April 5"]: The expression needs to be (hold onto your hats),
CDOW(Qmf_temp.evstart)+", "+CMONTH(Qmf_temp.evstart)+" "+ALLTRIM(STR(DAY(Qmf_temp.evstart)))

Explanation of the parts:
  • First, note that since we're going to be inserting/concatenating characters, every part of the expression must also be a character data type. If you don't watch this, the expression builder's Verify... feature will complain "Function argument value, type or count is invalid".
  • CDOW(Qmf_temp.evstart)+", "
    • Returns the name (i.e., character value) of the day of the week of the datetime field, and adds a comma and a space after
  • CMONTH(Qmf_temp.evstart)+" "
    • Returns the name of the month of the field, followed by a space
  • ALLTRIM(STR(DAY(Qmf_temp.evstart)))
    • Starting from the innermost command: returns the numerical value of the date via DAY(fieldname), converts it to a character via STR(), and then strips off any leading or trailing spaces with ALLTRIM()
For "hh:mm A/PM" (e.g., "10:00 AM"):
STRTRAN(RIGHT(TTOC(Qmf_temp.evstart),11),":00 "," ")

This is a messy one. PHP (my real language of choice) allows you to format a date just about any way you want. Unfortunately, Visual Fox Pro's suite of date format functions simply doesn't have what we need here, and you can't even build it the way I did the date solution above, because you can't add any logic into that little expression builder dialog box. So here's how this one works (from the inside out, as it were):
  • TTOC(Qmf_temp.evstart) returns the character version of the datetime field - the result is something like 04/05/2015 10:00:00 AM - note all the spaces mixed in there, plus the seconds, which we don't want...
  • RIGHT(...., 11) returns only the rightmost 11 characters from that long string, and
  • STRTRAN(..., ":00 "," ") removes the seconds bit (it matches ":00" plus a following space and replaces that chunk with a single space)
There's still a remaining niggle: if you have an event at 6pm, it'll currently show as 06:00 PM - the PHP version of the LTRIM() function lets you trim a given character as well as the traditional leading space, so I could in theory use it to trim a leading zero, but ah well...

If anyone can see a better way of doing this, I'm all eyes - otherwise, hope this helps someone else along the way...

Post Reply