Solved

Parse string into Datetime Visual Fox Pro 6.0

Posted on 2011-02-22
10
2,511 Views
Last Modified: 2012-05-11
Hello Experts,

I have a string where its a Datetime and I need to parse in a FoxPro datetime format.

ReturnString= 1009291928
i = 0
Year=SUBSTR(ReturnString,i+1,2)  result 10
Month=SUBSTR(ReturnString,i+3,2)  result 09
Day=SUBSTR(ReturnString,i+5,2)  result 29
Hour=SUBSTR(ReturnString,i+7,2)  result 19
Min=SUBSTR(ReturnString,i+9,2)  result 28

I need it in FoxPro Datetime format.

Thanks.
0
Comment
Question by:ASPDEV
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 250 total points
Comment Utility
DATETIME() function should help:


FUNCTION STR2DateTime
LPARAMETER ReturnString

LOCAL i, Year, Month, Day, Hour, Min
i = 0
Year=VAL(SUBSTR(m.ReturnString,m.i+1,2)) && result 10
Month=VAL(SUBSTR(m.ReturnString,m.i+3,2))&& result 09
Day=VAL(SUBSTR(m.ReturnString,m.i+5,2))  && result 29
Hour=VAL(SUBSTR(m.ReturnString,m.i+7,2)) && result 19
Min=VAL(SUBSTR(m.ReturnString,m.i+9,2))  && result 28

RETURN DATETIME(SET('CENTURY', 1)*100 + IIF(m.Year < SET('CENTURY', 2), 100, 0), m.Month, m.Day, m.Hour, m.Min)

Open in new window

0
 
LVL 3

Assisted Solution

by:AndrewJen
AndrewJen earned 150 total points
Comment Utility
ReturnString= "1009291928"
i = 0
Year=SUBSTR(ReturnString,i+1,2) && result 10
Month=SUBSTR(ReturnString,i+3,2) &&  result 09
Day=SUBSTR(ReturnString,i+5,2) && result 29
Hour=SUBSTR(ReturnString,i+7,2) && result 19
Min=SUBSTR(ReturnString,i+9,2) && result 28
* CTOT("2000-10-24T13:30:00")
mydatetime= CTOT("20"+year+"-"+month+"-"+day+"T"+hour+":"+min+":00")
? mydatetime
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
The function should use SET CENTURY ... ROLLOVER ....  setting to calculate the century part of the date.

And it contains a small bug, sorry.

RETURN DATETIME(m.Year + SET('CENTURY', 1)*100 + IIF(m.Year < SET('CENTURY', 2), 100, 0), m.Month, m.Day, m.Hour, m.Min)

Open in new window

0
 

Author Comment

by:ASPDEV
Comment Utility
pcelba & AndrewJen,

I tried both of your code, it didn't work.

Thanks.
0
 
LVL 12

Expert Comment

by:jrbbldr
Comment Utility
When you say:  "I tried both of your code, it didn't work."

How?  
Specifically what did not work?
  *  Did it throw an error message?
  *  Were the results just not as you expect?

With better clarity/detail, we can assist you better.

Good Luck


0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 100 total points
Comment Utility
The VFP function CTOT()  will convert a string of characters (if formatted correctly) into a DateTime value.

You should easily be able to take the individual memory variables:
       Year=SUBSTR(ReturnString,i+1,2)  result 10
       Month=SUBSTR(ReturnString,i+3,2)  result 09
       Day=SUBSTR(ReturnString,i+5,2)  result 29
       Hour=SUBSTR(ReturnString,i+7,2)  result 19
       Min=SUBSTR(ReturnString,i+9,2)  result 28
and put them into a properly formatted string and then run the function to get a DateTime value returned.

Look at your VFP Help for CTOT()

Good Luck
0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Pavel Celba's function returns the wrong year, true. He forgot to add the m.year to the year portion.

AndrewJen's Code works, but CTOD is depending on environment settings.

But you might have another error or it doesn't do anything to you, if you just execute pcelbas code as is, nothing happens, as it's just a function definition, you need to call it, eg STR2DateTime('1009291928').

Therefore jrbbldrs question is very valid: How do you call this? How is this embedded in your code?

I'll throw in another solution making use of the invariantly valid format {^YYYY-MM-DD hh:mm:ss}:

ReturnString='1009291928'
? EVAL('{^ 20'+TRANSFORM(m.ReturnString,'@R 99-99-99 99:99:00')+'}')

As you only have two digits for the year you'd need to also take care if putting that into 20XX is okay, or if you also need dates in the year 19XX if eg. XX>80 or year 21XX if eg XX<10.

Bye, Olaf.
0
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
"Pavel Celba's function returns the wrong year, true."

It seems you missed my fix posted five minutes later. :-)

0
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
Yes, I missed your fix. Maybe ASPDEV also did so.

Bye, Olaf.
0
 

Author Closing Comment

by:ASPDEV
Comment Utility
I made a mistake in my query earlier.

Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now