I have had such helpful responses so I'll try another. I would like to restrict the amount of data pulled from my SQL database to only the 5 days prior to when I load. One field in my data is ORDDATE so I would like to pull the latest 5 days.
I think I would use something like Where ORDDATE > Today()-5 or such.
I assume the WHERE clause is part of your SQL. Probably your SQL server don't understand Today(), but you can use a variable:
Let vThreshold = Num(Today()-5);
WHERE ORDDATE > $(vThreshold);
When I am getting the following:
The following error occurred:
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Error converting data type varchar to numeric.
Let vThreshold = Date(Today()-5,'MM/DD/YYYY');
LIB CONNECT TO 'svtrucosql03';
Date(Date#(ORDDATE, 'YYYYMMDD'), 'MM/DD/YYYY') as ORDDATE,
SQL SELECT ORDUNIQ,
FROM TRUDAT.dbo.OEORDH Where ORDDATE > '$vThreshold';
Not just because it gives me point, but because this might help someone else in the future if they have a similar issue and might find it easy to browse this discussion if you close the thread by marking correct and helpful responses.
Why do you think it didn't pull just a subset of my data? It appears as though the whole data set was pulled even though the where clause should have only take the portion within the 5 days.
Probably because you are comparing apple to oranges in the WHERE clause.
You would need to find a format for the variable that the DBMS can compare to the ORDDATE values in your DB table. Maybe just ask your local DB guru how this value needs to look like.