Solved

Parse string into Datetime Visual Fox Pro 6.0

Posted on 2011-02-22
10
2,737 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 250 total points
ID: 34955463
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
ID: 34955473
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 42

Expert Comment

by:pcelba
ID: 34955501
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:ASPDEV
ID: 34956412
pcelba & AndrewJen,

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

Thanks.
0
 
LVL 12

Expert Comment

by:jrbbldr
ID: 34957060
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
 
LVL 12

Assisted Solution

by:jrbbldr
jrbbldr earned 100 total points
ID: 34957089
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
ID: 34958916
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 42

Expert Comment

by:pcelba
ID: 34959493
"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
ID: 34963531
Yes, I missed your fix. Maybe ASPDEV also did so.

Bye, Olaf.
0
 

Author Closing Comment

by:ASPDEV
ID: 34974315
I made a mistake in my query earlier.

Thanks.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month11 days, 14 hours left to enroll

623 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