0

We are using Abra Suite Software that is using a VFP db. I have a small program in C# that I would like to use to retrieve data from the db and generate a csv file from it. At this point my problem is to get the data based on the range of date that I specified in the SQL statement. Below is my SQL statement and for whatever reason I see that there are records from 2008 (i.e. 06/09/2008). What am I doing wrong here? Because when I read how SQL works I should have been able to do "WHERE chkdate BETWEEN '2007-06-01' '2007-06-06'" but I always get 'Operator/operand type mismatch', that's why I am using CAST in my current SQL statement.

string SelectCmd = "SELECT TOP 100 p_empno, p_fname, p_lname, chknumber, chkamount, CAST(chkdate AS varchar(10)) " +
        "FROM hrpersnl " +
        "INNER JOIN prckhist ON hrpersnl.p_empno = prckhist.empno " +
        "WHERE CAST(chkdate AS varchar(10)) BETWEEN '06/06/2007' AND '06/09/2007' " +
        "ORDER BY p_empno";
Johan G
  • 1,064
  • 3
  • 16
  • 30
  • If you're converting everything to a varchar, you're going to be doing a string comparison, not a date comparison which is what I'm assuming you want. You probably need to change your date strings to date types to get it to work. In Oracle, it'd be something like 'TO_DATE(...)' not sure about your flavour of sql... – forsvarir May 23 '12 at 11:41
  • sorry I put the wrong code on the WHERE statement, I just updated it – Johan G May 23 '12 at 12:09
  • If you'd like to avoid having to learn the VFP syntax... you should check out http://linqtovfp.codeplex.com/ or http://vfpefprovider.codeplex.com/ – Tom Brothers May 23 '12 at 12:41

1 Answers1

3

Put braces instead of apostrophes around your two between dates, like {06/06/2007} AND {06/09/2007}

seekerOfKnowledge
  • 524
  • 5
  • 25
  • "WHERE chkdate BETWEEN {06/06/2007 AND 06/09/2007 } " I tried this but it gave me syntax error – Johan G May 23 '12 at 12:24
  • No no, {06/06/2007} AND {06/09/2007} – seekerOfKnowledge May 23 '12 at 12:24
  • hey that does it. awesome. may I ask why braces? I am just wondering because all the examples of the sql statements that I have found so far always use the apostrophes – Johan G May 23 '12 at 12:29
  • Honestly, nope. I've never used Abra Suite Software, nor have I ever used VFP. I was just searching around for your particular issue and came across http://www.pcreview.co.uk/forums/error-my-clause-please-help-t1375793.html which had your answer at the bottom. *shrug* – seekerOfKnowledge May 23 '12 at 12:31
  • 1
    Because VFP's date type uses curly braces as the delimiter. VFP is not a SQL backend, though it supports a fair amount of SQL. – Tamar E. Granor May 23 '12 at 21:09
  • Thanks for link. I also ended up finding out how to use Command Parameter since someone mentioned about it on that post – Johan G May 24 '12 at 17:08