sql server reporting services: Foxpro date syntax problem from SSRS 2005 using VFP ODBC driver. -- sql server reporting services

Foxpro date syntax problem from SSRS 2005 using VFP ODBC driver.

bmchan
8/14/2006 11:15:02 AM

sql server reporting services: Looking for a way to return records within a date range. Having problems with
parameter as a date syntax.

I usually just SSIS batch my FoxPro stuff into a SQL enviroment so I can
take full advantage of SQL Server 2005's familiar, fast and not too fusy
T-SQL syntax.

However I have a requirement for live data for a single report. Since I'm
using the VFP Driver I'm limited to the FoxPro syntax which I'm not too
familiar with. I can do pretty simple queries, but anything involving
filtering a date range I'm not having a lot of luck.

This works for me:
SELECT CHECKNO FROM CHECKS

This does not work for me:
SELECT CHECKNO FROM CHECKS WHERE CHECKDATE = ?

I am told that I need {} surrounding a date value. I've tried
SELECT CHECKNO FROM CHECKS WHERE CHECKDATE = {?}
which doesn't work.

If I hard code
SELECT CHECKNO FROM CHECKS WHERE CHECKDATE = {08/10/2006}
I get records the way I want to.

Can someone explain to me a way to filter my FoxPro results by using a date

RE: Foxpro date syntax problem from SSRS 2005 using VFP ODBC driver.

bmchan
8/14/2006 11:34:01 AM

Actually searching the newsgroup a little bit more I see Cindy Winegarden
already posted a solution. I've installed the new VFP OLE DB driver and it
works a lot better I don't even have to bother to use the {} around a date!

[quoted text, click to view]

Re: Foxpro date syntax problem from SSRS 2005 using VFP ODBC driver.

Bruce L-C [MVP]
8/14/2006 1:33:41 PM

Have you made sure that your parameter is of type datetime. In layout go to
the Report Menu->Report Parameters and make sure the datatype is datetime.

You shouldn't have to do anything. This syntax should work for you:
SELECT CHECKNO FROM CHECKS WHERE CHECKDATE = ?

When going against SQL Server it is able to tell the data type automatically
when creating the report parameters. With unnamed parameters (for ODBC) this
is not the case. It treats each parameter as text unless you explicitly tell
it otherwise.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Foxpro date syntax problem from SSRS 2005 using VFP ODBC driver.

Cindy Winegarden
8/17/2006 12:36:48 AM

Hi!

Just to be sure you (and any others reading here) understand, when FoxPro
dates are represented as text, for example in a SQL Pass-through query, they
are surrounded with curly braces. For example:
strSQL= "Select * From Table Where DateField = {08/16/2006}"

However, when working with a parameter you don't surround anything with the
curly braces, but let the OLE DB data provider handle "translating" the
parameter into something T-SQL can deal with appropriately.

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.