Here's a UDF to do the job (no doubt could be improved): LOCAL c As String, cDay As String c = "Friday,April 04,2014 6.45 PM" cDay = "" *!* First call the function to return just the datetime value ? STR2DATETIME(c) *!* Now call it to fill in cDay ? STR2DATETIME(c, @cDay) ? cDay
FUNCTION Str2DateTime(tcString As String, tcDay As String) As Datetime #DEFINE LOC_DELIM ", " #DEFINE LOC_MONTHS "Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec" LOCAL i As Integer, n As Integer, lcDay As String, lcMonth As String, lcTime As String ; lnYear As Integer, lnMonth As Integer, lnDay As Integer, p As Integer, lcHourFormat As String LOCAL ARRAY laTime[3] && Array to hold the time parts n = GETWORDCOUNT(tcString, LOC_DELIM) lcDay = GETWORDNUM(tcString, 1, LOC_DELIM) IF PCOUNT() = 2 tcDay = m.lcDay ENDIF lcMonth = PROPER(LEFT(GETWORDNUM(tcString, 2, LOC_DELIM),3)) FOR i = 1 TO 12 IF GETWORDNUM(LOC_MONTHS, m.i, ",") == m.lcMonth lnMonth = m.i EXIT ENDIF ENDFOR lnDay = VAL(GETWORDNUM(tcString, 3, LOC_DELIM)) lnYear = VAL(GETWORDNUM(tcString, 4, LOC_DELIM)) *!* Process the time portion lcTime = GETWORDNUM(tcString, 5, LOC_DELIM) lcTime = CHRTRAN(lcTime, ".,", ":") && Change commas and periods to colons STORE 0 TO laTime p = GETWORDCOUNT(lcTime, ":") FOR i = 1 TO p *!* i = 1 = Hours, i = 2 = Minutes, i = 3 = Seconds laTime[i] = VAL(GETWORDNUM(lcTime, i, ":")) ENDFOR IF n = 6 lcHourFormat = UPPER(GETWORDNUM(tcString, 6, LOC_DELIM)) IF lcHourFormat = "PM" AND laTime[1] < 12 laTime[1] = laTime[1] + 12 ENDIF ENDIF RETURN DATETIME(lnYear, lnMonth, lnDay, laTime[1], laTime[2], laTime[3]) ENDFUNC Laurie On 31 October 2014 13:01, AndyHC <[email protected]> wrote: > To be honest I never managed to be fluent in regex, so here's a command > line hack: > CLEAR > CLEAR MEMORY > x=[Friday, April 04, 2014 6:54 AM] > aa=[01january02february03march04april05may06june07july08august09 > september10october11november12december] > ampm=RIGHT(x,LEN(x)-RAT(' ',x,1)) > x=LEFT(x,RAT(' ',x,1)-1) > hhmm=RIGHT(x,LEN(x)-RAT(' ',x,1)) > x=LEFT(x,RAT(' ',x,1)-1) > yyyy=RIGHT(x,LEN(x)-RAT(' ',x,1)) > x=LEFT(x,RAT(' ',x,1)-1) > x=LEFT(x,RAT(',',x,1)-1) > dd=RIGHT(x,LEN(x)-RAT(' ',x,1)) > x=LEFT(x,RAT(' ',x,1)-1) > mmmm=RIGHT(x,LEN(x)-RAT(' ',x,1)) > s='-' > ?CTOT(yyyy+s+SUBSTR(aa,AT(LOWER(mmmm),aa)-2,2)+s+dd+'T'+hhmm+' '+ampm) > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/camvtr9ct_af5vqg5q9kwqzk0niam-4hfnydv8owh6_-tvn4...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
