DATE format in Foxpro – Microsoft Visual Foxpro

I am using Microsoft Visual Foxpro (Version: 06.00.8167.00) to read data from Foxpro data files for one of the analysis project, had tough time to query based on date column. I have tried at least 10 different options but of no use! Finally I found the solution, used DATE() function as follows to complete my work…

USE z:\xyz\Data\Main\TableName.DBF shared
SELECT * from TableName WHERE adddate < DATE()
SELECT * from TableName WHERE adddate > DATE(2014,10,05)
CLOSE DATABASE

NOTE:
DATE() considers todays date
DATE(yyyy,mm,dd) takes Year, Month and Date as inputs and converts into date format!
In above example, my WHERE condition includes all records greater than 5th October 2014

I have been getting errors with all these formats…
[CODE]
USE z:\xyz\Data\Main\TableName.DBF shared
SELECT * from TableName WHERE adddate > {1010/2014}
SELECT * from TableName WHERE adddate > ‘{1010/2014}’
SELECT * from TableName WHERE adddate > ‘{10/10/2014}’
SELECT * from TableName WHERE adddate > {10/10/2014}
SELECT * from TableName WHERE adddate > {05/10/2014}
SELECT * from TableName WHERE adddate > {05/05/2014}
SELECT * from TableName WHERE adddate > {10/05/2014}
SELECT * from TableName WHERE adddate > {2014/10/10}
SELECT * from TableName WHERE adddate > “2014/10/10”
SELECT * from TableName WHERE adddate > “10/10/2014”
SELECT * from TableName WHERE adddate > ’10/10/2014′
SELECT * from TableName WHERE adddate > ‘2014/10/10′
SELECT * from TableName WHERE adddate > “{10/10/2014}”
SELECT * from TableName WHERE adddate > {’10/10/2014′}
SELECT * from TableName WHERE adddate > {’10/10/2014’}
SELECT * from TableName WHERE adddate > {2014-10-05 00:00:00}
CLOSE DATABASE
[/code]

Error Messages for reference:
Foxpro_Errors_01

You may also like this article:
https://vijredblog.wordpress.com/2014/10/02/basics-of-foxpro-conversion-failure/

Advertisements
This entry was posted in Productivity and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s