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)
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement