Thanks to all for the suggestions. Ended up writing the following function:
FUNCTION Text2DTime
LPARAMETERS lcTxt
*Returns empty DateTime value if any values are incorrect
*Tested with the following values
* [Friday, September 19, 2014 10:58 AM]
* [Friday, September 9, 2014 8:02 PM]
* [Friday, September 9, 2014 8:02] &&
assumed to be AM
* [September 19, 2014 10:58 AM]
* [September 9, 2014 8:02 PM]
* [September 9, 2014 8:02]
&& assumed to be AM
* [April 24, 2013 19:01]
&& returns PM
IF TYPE("lcTxt")<>"C"
lcTxt = ''
ENDIF
LOCAL ltDTime
IF EMPTY(lcTxt)
ltDTime = DTOT({})
ENDIF
lcTxt = LOWER(ALLTRIM(lcTxt))
LOCAL lnYear,lnMonth,lnDay,lnHour,lnMinute,lnCommas
STORE 0 TO lnYear,lnMonth,lnDay,lnHour,lnMinute
lnMonth = ICASE( 'jan' $ lcTxt , 1 ;
,'feb' $ lcTxt , 2 ;
,'mar' $ lcTxt , 3 ;
,'apr' $ lcTxt , 4 ;
,'may' $ lcTxt , 5 ;
,'jun' $ lcTxt , 6 ;
,'jul' $ lcTxt , 7 ;
,'aug' $ lcTxt , 8 ;
,'sep' $ lcTxt , 9 ;
,'oct' $ lcTxt , 10 ;
,'nov' $ lcTxt , 11 ;
,'dec' $ lcTxt , 12 ,
0)
lnCommas = OCCURS([,] , lcTxt)
lnDay = VAL(SUBSTR(lcTxt , AT([ ],lcTxt,lnCommas) +1))
&& number after second space
lnYear = VAL(SUBSTR(lcTxt , AT([,],lcTxt,lnCommas) +1))
&& number after second comma
lnHour = VAL(SUBSTR(lcTxt , AT([:],lcTxt) -2))
&& number before colon
lnMinute = VAL(SUBSTR(lcTxt , AT([:],lcTxt) +1))
&& number after colon
IF RIGHT(lcTxt,2) == 'pm'
lnHour = lnHour + 12
ENDIF
IF lnYear>0 AND BETWEEN(lnMonth,1,12) AND BETWEEN(lnDay,1,31) AND
BETWEEN(lnHour,0,23) AND BETWEEN(lnMinute,0,59)
ltDTime = DATETIME(lnYear,lnMonth,lnDay,lnHour,lnMinute)
ELSE
ltDTime = DTOT({})
ENDIF
RETURN ltDTime
ENDFUNC && Text2DTime
By the way, I needed this to parse a datetime value from emails forwarded to
one of my email accounts. This is how outlook formats the original 'sent date'.
Customized Business Services, LLC (928) 580-6352
Dennis Schuette Primary:
[email protected]
350 West 16th Street, Suite 309 Alternate:
[email protected]
Yuma, AZ 85364 Map it
-----Original Message-----
From: ProfoxTech [mailto:[email protected]] On Behalf Of Laurie Alvey
Sent: Friday, October 31, 2014 6:33 AM
To: [email protected]
Subject: Re: Character to DateTime
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/[email protected]
** 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.
