Using SQL code, i need to create a string that automatically creates the dates between to seperate days

I am creating an automatically updating dashboard for repeating purposes and I need to automatically draw invoice values from our system between certain dates

i.e. on Tuesday, the reporting needs to show Mondays invoices.

Is it possible to create a string whereby dates are automatically set.

So far I have realized that DateAdd exists 🙂 I have created this.

SELECT * FROM [CData].[MYOB].[SaleInvoices] where Date>DATEADD('d', -1, CURRENT_DATE())  

But this returns all the invoices after this date. (as no end date specified) I need to limit this to an end date.

Has anybody got any ideas?

Answer

Have you tried the BETWEEN operator?

SELECT  *
FROM    CData.MYOB.SaleInvoices
WHERE   Date BETWEEN DATEADD('d', -1, CURRENT_DATE()) AND CURRENT_DATE()