Using Current Date parameters in the SQL command in the DB Expert

[bradlee27514]

I have a query pulling some data in a SQL command

something like

select
table.invoicenumber,
table.invoicedate
from
table


What I want to do is restrict the records in the SQL command based on the invoicedate field, something like


where
table.invoicedate =< currentdate and
table.invoicedate => (currentdate - 3)

So invoices 3 days or older up to today

I know how to do this inside Crystal Reports, however I want to do it in the SQL command before the data even gets there. This is for several reasons, one of which is to speed up the report running. Does such syntax exist in SQL, does it know what the current date is?

 

[fisheromacse]

i have not tried it via SQL expression/command but CURDATE() returns the current date in SQL

 

[lbass]

In a command that accesses an Oracle database, I use:

where
"table"."date" >= {fn curdate()}-5 to {fn curdate()}

-LB

 

[hollywoodhd]

Just throwing this out there...

Within CR Database Expert, I used date() to get current date, and was able to do some date range stuff. the currentdate in your example didnt work for me.

 

[bradlee27514]

Thanks all, got the syntax and used datediff:

where
datediff(day,table.date,getdate())
between 0 and 3