Last month out of SQL - VFP Variables within SQL commands

[LCannon]

I'm trying to get last month's records out of an SQL table from a VFP 7.0 program. Tried the following but get SQL syntax errors;

Rec_date=month(date())-1


STORE SQLCONNECT('SERVER') TO SrvConn

SQL="select * from emi where month(loaddate)=rec_date"
=sqlexec(dataConn, SQL, 'records')

How do you use VFP varibles with SQL commands in a VFP program?

 

[Mike Gagnon]

LCannon

Why don't you use the whole date, instead of just the month, since your "loaddate" seems to be a date field as well.

Rec_date=gomonth(date(),-1)

STORE SQLCONNECT('SERVER') TO SrvConn

SQL="select * from emi where loaddate=rec_date"
=sqlexec(dataConn, SQL, 'records')

Mike Gagnon

 

[danceman]

SQL="select * from emi where month(loaddate)=";
+ rec_date

since this is a foxpro database, why are you using sql pass through?
Attitude is Everything

 

[rgbean]

The big problem with:
Rec_date=month(date())-1
is when today's date is in January - you'll have a month value of 0 (zero)!

I'd go with a variant of Mike's code:

tmpdate = gomonth(date(), -1) && get into last month
startdate = date(year(tmpdate), month(tmpdate),1) && first day
enddate = gomonth(startdate, 1)-1 && last day of last month

SQL="select * from emi where loaddate BETWEEN ";
 +"{d '"+TRANSFORM(DTOS(startdate), "@R xxxx-xx-xx")+"'}";
 +" AND ";
 +"{d '"+TRANSFORM(DTOS(enddate), "@R xxxx-xx-xx")+"'}"

This is partially based on the info in

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q145817

- "HOWTO: Formatting a Date or Time Value to Query an SQL Table".

Rick

 

[Mike Gagnon]

Rick

Wouldn't this function be easier to find the first day of last month?

SET DATE MDY
SET CENTURY ON
?CTOD(STUFF(DTOC(GOMONTH(DATE(),-1)),4,2,"01"))

Just kidding

Mike Gagnon

 

[Mike Gagnon]

Rick
While I'm at it I might as well through in the last day of that's function for free:

?(CTOD(STUFF(DTOC(GOMONTH(DATE(),0)),4,2,"01"))-1)

Mike Gagnon

 

[danceman]

an old dog can learn new tricks. I think of all the hard work that I use to do to manipulate dates with adding and subtracting to get what I needed for between.

GOMONTH() is new for me, or maybe I had a senior moment, THANKS GUYS Attitude is Everything

 

[Mike Gagnon]

Danceman

The only word of caution with GOMONTH function is it's not very good for historians since it doesn't work before the year 1752

Mike Gagnon

 

[rgbean]

Mike,
The only reason I don't use the STUFF() techique, is because it requires (or assumes)
SET DATE MDY
SET CENTURY ON

In the general case, you should also save off the current settings and then restore them afterwards. I really hate having to write all that extra code now that we have the uniform DATE(yyyy, mm, dd) function - it works so well with DTOS() and the strict date constant format - {^yyyy-mm-dd} (Notice how similar it is to SQL server date format - {d 'yyyy-mm-dd'}.)

Rick

 

[Mike Gagnon]

SET DATE MDY
SET CENTURY ON

Only use that, because I live in Canada, and our date setting is more like DMY, I was trying to make it more "americanized". But I assume that most applications installed will "know" the date stetting. But your suggestion is probably more "generic".


Mike Gagnon

 

[LCannon]

First off, I can't thank you all enough for this help. I'm so green with VFP & SQL that I appricate all the help I can get.

Magagnon,
Tried your code and got a similar SQL error that I have been getting: Invalid column name 'rec_date'.
The loaddate is a date field that contains any date. My purpose is to grab all the records within a given month.

Danceman,
Tried your code and got a "operator/operand type mismatch" error.
I'm trying to create a foxpro table from data in SQL. It's easier for me to work with that way.

Rgbean,
Tried your code and didn't get any errors nor did I get any records. Is there something in that code that I need to change?
Thanks for the link. Will read it.

All,
I figure what I really need to know is how to pass a variable within VFP to the commands I'm sending to SQL. I have tried using a question mark "?" in front of the variable name within the SQL command and that seems to be working:

emi_month=ALLTRIM(STR(MONTH(DATE())-1))

SQL="select * from emi where MONTH(loaddate)=?emi_month"

Still have to work with it a bit. But any other suggestions you all have is welcomed. Thanks again...

 

[danceman]

Tried your code and got a "operator/operand type mismatch" error.
I'm trying to create a foxpro table from data in SQL. It's easier for me to work with that way.


SQL="select * from emi where month(loaddate)=";
+ alltrim(str(rec_date))

I forgot that the var is numeric, need to convert.

A method I used before is to make my string, write it ti a file. open the file and review the result syntax. if it looks good, copy the sql statement, go to the command window, paste and execute it. Attitude is Everything

 

[rgbean]

When I run the code I provided, the SQL statement evaluates to:
select * from emi where loaddate BETWEEN {d '2002-08-01'} AND {d '2002-08-31'}

Obviously if your table doesn't have any data where LoadData is in the month of August 2002, you'll get no results. Perhaps you'll need to use something other than DATE() in the tmpdate line to get to valid data.

Rick

 

[LCannon]

Rgbean,

I think I must have miss read your code. Didn't enter the dates, just left the xx's in place. But I don't want to have to manually change the SQL select statement each month. Want to do it with a variable.

 

[rgbean]

Now I think I may have confused you. I'm saying that the code when run will generate a value of "select * from emi where loaddate BETWEEN {d '2002-08-01'} AND {d '2002-08-31'}" in your SQL variable. The 'x's in the transform() functions are used to generate the date values as shown.

An important point is that the SQL server can't "see" any FoxPro variables, that's why you can't simply pass them in. You need to first create a statement that is complete as far as the server is concerned - that's what I'm evaluating the required variables. Another way to do this is to write a stored procedure on the Server side, and then just pass the FP values into it, however that can be a lot less flexible.

Rick

 

[danceman]

SQL="select * from emi where month(loaddate)=";
+ alltrim(str(rec_date))
Attitude is Everything