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...