Ed,

Here are my two functions to convert back and forth between date/datetime vars and strictdates. The first one has maybe more params than what you might want but I wanted to write it once to do a number of things; simplify it if you like; the second one is also handy for converting back. Some of the wrapping might also be off so check that before running it. Also, these are fairly new so they might not yet be bulletproof. If you have any suggestions, send them on.

Albert


PROCEDURE DateTimeToStrictDate
* procedure to convert a date to a non-amiguous (character) strict date
* that can be used in FoxPro when SET STRICTDATE > 0; ideas came from
* FPAdvisor article, August 1999, page 46, Tip called "A More Universal
* cDateFormat() Function"; I have mainly added more parameters and checks

* Written by: Albert Gostick
* Last Updated: Jan 26, 2000

* Parameters:
* tdDateTime: date or datetime to convert
* tcDateDelimiter: delimiters to add to either end of the resulting
* string; optional; if nothing passed, default to adding curly braces
* ie. {}; put as an option as some SQL languages require delimiting
* with single quotes; if empty string explicitly passed, then NO
* delimiters added as some SQL applications don't want delimiters
* tcDateSeparator: character separator for date expressions; optional,
* if not passed, defaults to SET MARK TO character (but this also
* has to be either a hyphen, a forward slash, a period or a space);
* reverts to forward slash if none of the above
* tlOmitTime: if a datetime var is passed, if this is set to .T., only
* returns the date portion of the datetime ie. ignores the time

* Returns: date as character string in non-ambiguous format ie.
* {^yyyy/mm/dd hh:mm:ss} or returns an empty date if date passed in
* is empty to start with (ie { / / })

LPARAMETERS tdDateTime, tcDateDelimiter, tcDateSeparator, tlOmitTime

LOCAL lcVarType, lcLeftDelimiter, lcRightDelimiter, lcStrictDate, lcHours, ;
lcMinutes, lcSeconds


*** parameter checks and defaults ***

* if no date passed in, return null character

STORE TYPE("tdDateTime") TO lcVarType

IF NOT lcVarType $ "DT"
RETURN ""
ENDIF

* (going to check for empty dates first instead of doing rest of parameter
* checks since no real need to do those if we are dealing with an empty date)

*** Empty Date ***

* Note: return a fully padded date string so that it has, pretty well, the
* same length as a date that has been converted; but do not insert the
* strictdate character (a carat ^) as that causes a bomb if the user then
* tries to EVAL() it or use it when it is an empty date string; decided to
* add a space where the carat would be so that if this is called in a SQL
* statement (for example) the length returned is the same as a character
* strictdate so that the column width is correct

IF lcVarType = "D" AND EMPTY(tdDateTime)
RETURN "{ / / }"
ENDIF

*** Empty Date Time ***

* Note: the spaces in the below are not necessary and I tried then converting
* the below with varying spaces (no spaces in date or time, spaces just in
* date, spaces all) and they all convert to a datetime var okay; returned with
* a space for the carat for reasons outlined above; did not put room in for
* am or pm as not sure if I need; programmer can always convert to military
* time or pad up if done in a SQL statement (or proc here could be changed)

IF lcVarType = "T" AND EMPTY(tdDateTime)
RETURN "{ / / , : : }"
ENDIF


*** Back to paramater checks ***

* check for the delimiter passed in; following case statement is important
* in its order as user can pass an empty string to indicate no delimiters

DO CASE

* check first if user passed an empty string as this means that no
* delimiters should be used

CASE TYPE("tcDateDelimiter") = "C" AND EMPTY(tcDateDelimiter)
STORE "" TO lcLeftDelimiter, lcRightDelimiter

CASE TYPE("tcDateDelimiter") = "C"

* check that it is either a curly brace or a single or double quote
* and if not, drop back to our default curly braces

IF NOT tcDateDelimiter $ "{}'" AND tcDateDelimiter # ["]
STORE "" TO tcDateDelimiter
ENDIF

* now stuff the left and right delimiters; if it is not a curly
* brace, can just stuff both halves with passed in value

IF EMPTY(tcDateDelimiter) OR tcDateDelimiter $ "{}"

STORE "{" TO lcLeftDelimiter
STORE "}" TO lcRightDelimiter

ELSE

STORE tcDateDelimiter TO lcLeftDelimiter, lcRightDelimiter

ENDIF

CASE TYPE("tcDateDelimiter") # "C"

STORE "{" TO lcLeftDelimiter
STORE "}" TO lcRightDelimiter

ENDCASE

* check for the date separator passed in

IF TYPE("tcDateSeparator") # "C"
STORE "" TO tcDateSeparator
ENDIF

* check that it is one of the allowable ones (hyphen, forward slash,
* period or space)

IF NOT tcDateSeparator $ "-/. "
STORE "" TO tcDateSeparator
ENDIF

* if still empty, fish out the separator using SET MARK; but only
* do this if the date separator is not exactly equal to one space
* as this is a legitimate value

IF NOT tcDateSeparator == " "

STORE SET("Mark") TO tcDateSeparator

* it could still be empty since SET("Mark") returns null if
* the separator is a forward slash (the default); so if still
* empty, stuff in the forward slash

IF EMPTY(tcDateSeparator)
STORE "/" TO tcDateSeparator
ENDIF

ENDIF


*** ready to convert the date ***

* note: I started to do this with LTRIM(STR(YEAR(tdDateTime))) etc but the
* below actually has a lot less function calls

* checks: DTOS() returns a 4 digit century regardless of SET CENTURY

* Note: we do not have to check for invalid dates as they would have been
* caught above when checking for an empty date; cannot even stuff a date
* time with an empty date but a valid time so if an invalid datetime is
* passed in, it will always be empty and be caught above

STORE LEFT(DTOS(tdDateTime),4) + tcDateSeparator + SUBSTR(DTOS(tdDateTime),5,2) + ;
tcDateSeparator + SUBSTR(DTOS(tdDateTime),7,2) TO lcStrictDate

* add in the date delimiters and the strict date ^ sign

STORE lcLeftDelimiter + "^" + lcStrictDate + lcRightDelimiter TO lcStrictDate


*** Time section ***

* if a date time var was passed in and the programmer has not indicated
* that they want to suppress the inclusion of the time portion, convert
* this and add it in

IF lcVarType = "T" AND NOT tlOmitTime

* tested and HOURS() is not affected by SET HOUR TO 12/24 so no need
* to set or release this; pad all numbers with leading 0's for uniformity

STORE PADL(LTRIM(STR(HOUR(tdDateTime))),2,"0") TO lcHours
STORE PADL(LTRIM(STR(MINUTE(tdDateTime))),2,"0") TO lcMinutes
STORE PADL(LTRIM(STR(SEC(tdDateTime))),2,"0") TO lcSeconds

* add these into our string delimited with colons and with a space
* in between the date and the time (both a comma and a space are
* permissable; I am not sure about SQL syntax yet therefore, since
* FoxPro seems to return them with a space more often, will use that
* and hopefully it will be the best default

STORE lcStrictDate + " " + lcHours + ":" + lcMinutes + ":" + ;
lcSeconds TO lcStrictDate

ENDIF

RETURN lcStrictDate


PROCEDURE StrictDateToDateTime
* procedure to convert a StrictDate[Time] character string to a true
* date variable; this conversion is done by using the optional parameters
* in DATE() and DATETIME() to convert; since these functions require
* that the year, day and month be passed as numeric, it means that we
* would continually have to parse the character string and pull out each
* of these values, hence the function

* Written by: Albert Gostick
* Last Updated: Jan 26, 2000

* Parameters:
* tcStrictDate: strict date or datetime string to convert ie. can be in the
* format {^2000/01/26} or {^2000/01/26 12:23:05};

* Returns: a date variable, empty if strictdate was empty

LPARAMETERS tcStrictDate

LOCAL llHasDate, ;
;
lnYear,lnMonth,lnDay,lnHours,lnMinutes,lnSeconds, ;
;
llDateOkay,lcOldOnError,ldTestDate,llTimeOkay, ;
;
ldReturnDate

* default on some vars
STORE .F. TO llHasDate

* parameter check, has to be character

IF TYPE("tcStrictDate") # "C"
STORE "" TO tcStrictDate
ENDIF

* parameter also has to be at least 13 chars long eg. "{^2000/01/26}"
* or something is wrong

IF LEN(tcStrictDate) >= 13
STORE .T. TO llHasDate
ENDIF

* if date is empty, just return an empty date string; note: I tested
* and there should NOT be a carat (^) in an empty date; also, if there
* is no date, even if there is a time in the string, cannot create a
* DateTime var so just return as well

IF NOT llHasDate
RETURN {//}
ENDIF

* otherwise, go ahead and parse the string and convert

STORE VAL(SUBSTR(tcStrictDate,3,4)) TO lnYear
STORE VAL(SUBSTR(tcStrictDate,8,2)) TO lnMonth
STORE VAL(SUBSTR(tcStrictDate,11,2)) TO lnDay

* Design: originally I just tested to see that lnYear etc fell within
* their prescribed limits (eg. Month >=1 and <= 12); but this does not
* test to see if the date combination is an actual valid date (eg.
* Sept 31st is a bad date; therefore, easiest thing to do is to trap for
* an error trigger and just send the date off to that

STORE .T. TO llDateOkay

* save old error condition
STORE ON("Error") TO lcOldOnError

* set up error trap
ON ERROR STORE .F. TO llDateOkay

STORE DATE(lnYear,lnMonth,lnDay) TO ldTestDate

* reset the error program
ON ERROR &lcOldOnError

* if not okay to go on, just return an empty date as we cannot
* do the time anyhow

IF NOT llDateOkay
RETURN {//}
ENDIF

* now parse for the time information; have to check to see if even
* in string; a string with valid time should be at least 22 chars;
* will discard also if too long as something must be funny

* default whether time is okay to .F.
STORE .F. TO llTimeOkay

IF LEN(tcStrictDate) = 22

* parse out the time into vars
STORE VAL(SUBSTR(tcStrictDate,14,2)) TO lnHours
STORE VAL(SUBSTR(tcStrictDate,17,2)) TO lnMinutes
STORE VAL(SUBSTR(tcStrictDate,20,2)) TO lnSeconds

* these ranges can just be tested numerically to see if
* they all fall into acceptable ranges

IF lnHours >= 0 AND lnHours <= 23 AND lnMinutes >= 0 AND ;
lnMinutes <= 59 AND lnSeconds >= 0 AND lnSeconds <= 59

STORE .T. TO llTimeOkay

ENDIF

ENDIF

* convert based upon whether date or time or both okay; cannot
* just pass only date stuff to DATETIME() as it defaults to a
* datetime var with the date and midnight (00:00:00) as the time
* which we probably do not want; we have already covered off if
* date is not okay by returning above so only two options

IF llDateOkay AND llTimeOkay

STORE DATETIME(lnYear,lnMonth,lnDay,lnHours,lnMinutes,lnSeconds) ;
TO ldReturnDate

ELSE && only date okay

STORE DATE(lnYear,lnMonth,lnDay) TO ldReturnDate

ENDIF

* return this value
RETURN (ldReturnDate)