Current date/time in SQL statement for date compare?

When I attempt to use Get:

WHERE shipments.shipdate >= Get ( CurrentDate ) - 5 (in brackets or not)

results in:

ODBC Error: [Actual][SQL Server] 'Get' is not a recognized function name.

hang on, hang on!
are you querying filemaker from another database or querying another database from filemaker?
get(currentdate) is a filemaker function, and then works only when filemaker is querying a foreign database, but it cannnot be used as such in the query.
a SQL query in filemaker is a text string therefore it should be like:

"WHERE shipments.shipdate >= " & year(Get ( CurrentDate )-5) & month (Get ( CurrentDate )-5) & day (Get ( CurrentDate )-5)
for an ISO date

I should remember to be more specific...
I am importing data from SQL via ODBC by using Filemaker's "Import Data --> ODBC Data Source" function.  I specify the ODBC connection data, and am then taken to Filemaker's simplified query builder.  The results of this query are imported into a Filemaker table, where it is then shared.  Everything is working great, just looking to have that dynamic date.

My current query looks like:
SELECT "ShipmentItems"."MainReference", "Shipments"."ShipDate", "Shipments"."ShippedTime" AS [Time], Shipments.Addressee, ShipmentItems.ItemDescLine1 AS Description, Shipments.CarrierN AS [Tracking Number], ShipMethods.Description AS Method, ShipmentItems.ActualCharge AS Charge

FROM (ShipmentItems INNER JOIN Shipments ON ShipmentItems.ShipmentNumber = Shipments.ShipmentNumber) INNER JOIN ShipMethods ON Shipments.ShipMethodN = ShipMethods.KeyN

WHERE shipments.shipdate >= 3/01/2006 AND Shipments.UpdatedStatus< 2

order by shipments.shipdate DESC

Forgot  the dashes in between date strings:
"WHERE shipments.shipdate >= " & year(Get ( CurrentDate )-5) & "-" & month (Get ( CurrentDate )-5) & "-" & day (Get ( CurrentDate )-5)

If you do the query fromp another base to filemaker, which language do you use as this may slightly change:
see also this thread:
http://www.experts-exchange.com/Databases/FileMaker/Q_21656419.html

Do not take my last post into account, I did it before yur last, I'll be back in 20mn to have a look at it, I have to go now

OK, in this case, yr date format depends on the queried database, so you must reformat the filemaker date got from
Get ( CurrentDate )-5
as a compatible format for SQL, and that should be what I wrote in my previous post.
Not 100% sure, it may follow the US domestic format if your example if March 1st instead of January 3rd, in which case it would be:
month (Get ( CurrentDate )-5)  & "-" & day (Get ( CurrentDate )-5) & "-" & year(Get ( CurrentDate )-5)
Should work also work with a '/' instead of '-'  if yr example works.

Still the issue of the error in my first comment- Get is not a recognized function name...

Do you type this query directly in the SQL import dialog box or use a script? 1st solution won't work, it is supposed to be raw text and is not interpreted.

Make a script which uses:
import records/ specify data source / calculated SQL txt.
There you stick in yr complete query including the Get() which will be interpreted before execution.

I see where you are going, but once I get to the calculated SQL text entry, it only allows me to select fields in my Filemaker db (the one I'm trying to import INTO).  It doesn't see the ODBC db's and errors "Cannot find Table" if I try to SELECT from any SQL tables.

true, you must know the names you are querying from.
actually, you can copy/paste the query from the wizard you use once.
and it shows you the fields to take/calculate values from them, and insert them in the query like getting a date, or the complete query (which is my method most of the time. Most of the time you use that to concatenate the constant and variable part of a query; in yr case the variable will be the date from get(currendate) which a function, not a field, but it could also have been from a field entered by the user before querying.
so consider this as a tool to build up the query before executing it, and it is either static using the wizard / typing it by hand or interpreted if you use the calculated SQL text. In this last case only, functions and records will calculated or replaced by their actual value at execution time.
am I clear?

That is clear and would work perfectly if it weren't for ODBC.  I cannot "OK" out of the "Specify Calculation" screen when trying to calculate the text query.  It will only allow Filemaker tables and nothing more.

If you can't quit, it's a syntax error.
Can you copy/paste the string you did in this SQL calculation window you can't close, please.
I'll locate the syntax error.

This is the query that I am trying to paste into the Calculated SQL text screen.  FYI- The name of my Filemaker table (that this query is populating) is named "ODBC"

SELECT "ShipmentItems"."MainReference", "Shipments"."ShipDate", "Shipments"."ShippedTime" AS [Time], Shipments.Addressee, ShipmentItems.ItemDescLine1 AS Description, Shipments.CarrierN AS [Tracking Number], ShipMethods.Description AS Method, ShipmentItems.ActualCharge AS Charge

FROM (ShipmentItems INNER JOIN Shipments ON ShipmentItems.ShipmentNumber = Shipments.ShipmentNumber) INNER JOIN ShipMethods ON Shipments.ShipMethodN = ShipMethods.KeyN

WHERE shipments.shipdate >= Get (CurrentDate) - 5 AND Shipments.UpdatedStatus< 2

order by shipments.shipdate DESC

the fm rule in a calc window is that contant expressions must be in between quotes. expressions without quotes are functions or field names.
if you quotes in text string, you must double them or put a \ before them.

forget to mention that if you need a mix, you shoud concatenate using & like:
"This is my name: " & myname_field
Same thing applies to queries, it is just a bit more confusing since you have quotes all over the place. single quotes should be accepted with most ODBC drivers also. in this case, it is easier to read, you could use:

"SELECT 'ShipmentItems'.'MainReference', 'Shipments'.'ShipDate', 'Shipments'.'ShippedTime' AS [Time], Shipments.Addressee, ShipmentItems.ItemDescLine1 AS Description, Shipments.CarrierN AS [Tracking Number], ShipMethods.Description AS Method, ShipmentItems.ActualCharge AS Charge

FROM (ShipmentItems INNER JOIN Shipments ON ShipmentItems.ShipmentNumber = Shipments.ShipmentNumber) INNER JOIN ShipMethods ON Shipments.ShipMethodN = ShipMethods.KeyN

WHERE shipments.shipdate >= " & Get (CurrentDate) - 5 & " AND Shipments.UpdatedStatus< 2

order by shipments.shipdate DESC"

That query now runs!  However, it only returns records with no shipdate (there are a few).  Is there a way to see the actual query that it is dynamically building?

yes define a $variable with the same contents as the query definition. (copy/paste the whole expression)
use custom dialog to show $variable before executing it

Thanks much for your help!