Wiki Home

String To Date


Namespace: WIN_COM_API
See Also Cvt YYYYDDD 2 Date
10/23/2002 - Steven Blake The reverse operation to the DTOS() function has always seemed a curious omission in the supplied arsenal from Microsoft, especially before the introduction of the Strict Date Format. Of course, it's easy enough to remedy with a UDF (User Defined Function) to validate and to convert the CCYYMMDD data string occurring mostly in download files.
Although SQL - Date Types, both datetime and smalldatetime, can accept the DTOS() format as input, there is a limited range to the dates that are valid. My long time (DOS days) function STOD() had to be modified to test input for Microsoft® SQL Server™ front end applications. The current version of STOD() now also enforces those date limits within FoxPro but that has not presented a problem (yet).
Empty dates, available as blanks in FoxPro, convert to January 1, 1900 in SQL Server which is not only a valid date but also usually within the range of user input. So I can force NULL results in STOD() when there is an empty date.
The additional case statements evaluating the date parts are the result of a one-time bug in either FoxPro or dBase (or both.) A leap year date could be returned for any year at end of a century (i.e., 1800, 1900) when it did not actually occur, so I took over the audit to prevent those incorrect extra days.
**************************************************************************
* Function: STOD()
*  Purpose: Converts DTOS() character string back into date value
* Argument: cWhatDay - in CCYYMMDD sequence; punctuation will be removed
*           lLetNull - Empty dates become 01/01/1900 in SQL; force NULL
*           lIsSmall - SQL destination is smalldatetime datatype
**************************************************************************
LPARAMETER cWhatDay, lLetNull, lIsSmall
LOCAL dReturnD, cRawDate, cTheYear, cTheMnth, cThe_Day

*-- If parameter lLetNull then default return value to null
dReturnD = IIF(lLetNull, .NULL., {})

*-- Remove punctuation that may be present in date string parameter
cRawDate = IIF(VARTYPE(cWhatDay) = "C", CHRTRAN(cWhatDay, "\-/", ""), SPACE(8))
cTheYear = LEFT(cRawDate, 4)
cTheMnth = SUBSTR(cRawDate, 5, 2)
cThe_Day = RIGHT(cRawDate, 2)

DO CASE
CASE lIsSmall .AND. !BETWEEN(VAL(cRawDate), 19000101, 20790606)
   *-- Smalldatetime value must be between January 1, 1900 and June 6, 2079

CASE !BETWEEN(VAL(cRawDate), 17530101, 99991231)
   *-- Datetime value must be between January 1, 1753 and December 31, 9999

CASE !BETWEEN(VAL(cTheMnth), 1, 12)
   *-- Month value should be between 1 and 12

CASE !BETWEEN(VAL(cThe_Day), 1, 31)
   *-- Day value should be between 1 and 31

CASE INLIST(VAL(cTheMnth), 4, 6, 9, 11) .AND. !BETWEEN(VAL(cThe_Day), 1, 30)
   *-- Day value should be between 1 and 30

CASE MOD(VAL(cTheYear), 400) = 0 .AND. ;
   VAL(cTheMnth) = 2 .AND. !BETWEEN(VAL(cThe_Day), 1, 29)
   *-- Day value should be between 1 and 29 (leap year)

CASE MOD(VAL(cTheYear), 100) > 0 .AND. MOD(VAL(cTheYear), 4) = 0 .AND. ;
   VAL(cTheMnth) = 2 .AND. !BETWEEN(VAL(cThe_Day), 1, 29)
   *-- Day value should be between 1 and 29 (leap year)

CASE MOD(VAL(cTheYear), 400) > 0 .AND. ; 
   (MOD(VAL(cTheYear), 100) = 0 .OR. MOD(VAL(cTheYear), 4) > 0) .AND. ;
   VAL(cTheMnth) = 2 .AND. !BETWEEN(VAL(cThe_Day), 1, 28)
   *-- Day value should be between 1 and 28

OTHERWISE
   *-- This should be a good date value
   dReturnD = CTOD("^" + cTheYear + "/" + cTheMnth + "/" + cThe_Day)

ENDCASE

RETURN dReturnD

Category Code Samples Category UDF
( Topic last updated: 2004.04.19 03:25:17 PM )