Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

VFP 9 DateTime and MySQL

Posted on 2010-11-09
8
Medium Priority
?
2,755 Views
Last Modified: 2012-05-10
Hi all. I use VFP to connect to a MySQL db.

How would I insert into the db the value "11/9/2010" when the db field is set to DATETIME?
I'm assuming a FP function will be needed to append time as "00:00:00"  in order for the field to accept it?

If so, how then would I retrieve the date from the db for use in a VFP form where only the date in 11/9/2010 format is required?

In short what I'm asking is what are the two FP functions to 1) convert the date to a DATETIME for insertion into the db, and 2) on retrieval, strip the time portion so only the date is left.

Thanks!

0
Comment
Question by:formadmirer
[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
  • 5
  • 3
8 Comments
 
LVL 43

Expert Comment

by:pcelba
ID: 34097834
1. Convert the date to datetime: DTOT()

aaa = {^2010.11.09}
bbb = DTOT(aaa)
? bbb

2. Strip the time portion: TTOD()

aaa = {^2010.11.09 23:37:50}
bbb = TTOD(aaa)
? bbb

I would guess the ODBC driver is able to convert some values automatically. So, if you are inserting date to the datetime column then the time part is added automatically and vice versa.

0
 

Author Comment

by:formadmirer
ID: 34098193
Thanks. I've been working for hours on this and can't seem to get it right.

I have yet to tackle the retrieving part, I'm still stuck on inserting.

In the csv source file the following dates are provided. Five records total, two with the dates below and the remaining three empty:

11/09/10

11/12/10

When I run the dates through my code below, the wait window shows every one with today's date/time.

I am trying to get it so that IF the supplied date is more than 90 days in the future, datetime for that item is set to datetime() + 90.  It's not working.





DO CASE
	CASE !EMPTY(lcETA)
		ldETA = CTOT(lcETA)
	OTHERWISE
		ldETA = DATETIME()
ENDCASE
	
DO CASE
	CASE ldETA <= DATETIME()
		ldETA = DATETIME()
	CASE ldETA >= (DATETIME() + 90)
		ldETA = (DATETIME() + 90)
	OTHERWISE
		ldETA = DTOT(ldETA)
ENDCASE

WAIT WINDOW "ldETA = " + TTOC(ldETA)

Open in new window

0
 

Author Comment

by:formadmirer
ID: 34098301
Sorry, forgot - lcETA is the variable assigned to the date from the csv file.

FWIW, if I simply directly insert the date into the MySQL db it doesn't work either.

A character date of '11/12/10', inserted, becomes '12/10/2011 12:00:00 AM' in the MySQL db.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:pcelba
ID: 34098325
It is probably caused by line 10: ldETA = DATETIME()

Also SET DATE setting could affect results.

The best way you can do it to place SET STEP ON as the first statement of your code. Then you will see what line of your code is executed and what values are in your variables.
0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 34098348
Use such date formats which are not ambiguous.

MySQL could support:  '2010-11-09'
VFP allows:  {^2010.11.09}

If you are inserting date then you may store it into a variable and then you may use this variable with ? prefix in SQLEXEC command:

ldDate = {^2010.11.09}
SQLEXEC(1, "INSERT INTO SomeTable VALUES (?ldDate)"

0
 

Author Comment

by:formadmirer
ID: 34098709
Thanks pcelba.

I've finally got both insert working, thanks to your suggestions.

As for retrieval, I simply DTOS the value from the MySQL db, and hacked the string using three different SUBSTR to finally arrive at a usable yyyy-mm-dd string.

Not elegant I know, but it works.

Thanks again!

btw - what is the purpose of the ^ as you used it above?
0
 
LVL 43

Expert Comment

by:pcelba
ID: 34100101
{^yyyy.mm.dd} is a standard VFP format for date constant - it has date data type and you don't need any conversion.

Similarly {^yyyy.mm.dd hh:mm:ss}  is a datetime literal constant in VFP.

The caret symbol (or Circumflex accent in Unicode terminology) just says "This constant is in unambiguous format". Without that character the constant would be interpreted based on the SET DATE setting during COMPILE time.

To obtain date string in YYYY-MM-DD format you may also use:

SET DATE YMD
SET MARK TO "-"
SET CENTURY ON
? DATE()
? DTOC(DATE())

DTOS() is better in certain situations because it is SET DATE independent.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 34100115
Just FYI, FoxPro checks date constants and conversions validity and you may affect the strictness of these checks by appropriate SET STRICTDATE TO ... setting. See more: http://msdn.microsoft.com/en-us/library/wbzwk155(VS.80).aspx
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
With so many activities to perform, Exchange administrators are always busy in organizations. If everything, including Exchange Servers, Outlook clients, and Office 365 accounts work without any issues, they can sit and relax. But unfortunately, it…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 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