Using DateDiff in a SELECT statement

[dominicall]

Here's an interesting one.

I have a database in the site I'm developing which contains invoice information (amount, date, datepaid, etc...)

Each day I'll need to run an email to those unsavoury clients that haven't paid their invoices in time - we all suffer it unfortunately.

I've been trying to work this out myself and have had a look on the web but can't seem to work out how to use the datediff function is a SELECT statement.

Basically, I want to select the records for any clients where the difference in date between Now and the Invoice Date is greater than 30 days and the invoice paid = 0 (i.e. invoice not paid)

Anyone help me out on this one???

Thanks

Dominic :confused:

[whammy]

"SELECT * WHERE DateDiff(""d"",databaseDate," & Now() & ") > 30"

should work...

check out:

http://www.w3schools.com/vbscript/func_datediff.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acrefComparingVBSQLServerFunctions.asp

:)

[dominicall]

LOL - I kind of had an idea you'd reply quickly when I saw you online....

I'll test it out but therefore I assume that something like this will work...

Dim DateNow : DateNow = Now()

"SELECT tbl_Offices.Email AS Email, tbl_Offices.Agency AS Agency, tbl_Invoices.InvoiceNum AS InvoiceNum, tbl_Invoices.InvoiceDate AS InvoiceDate, tbl_Invoices.InvoiceAmount AS InvoiceAmount FROM tbl_Invoices LEFT OUTER JOIN tbl_Offices ON tblInvoices.OfficeID = tbl_Offices.OfficeID WHERE DateDiff(""d"",tbl_Invoices.InvoiceDate," & DateNow & ") > 30 AND tbl_Invoices.InvoicePaid = 0"

Does that look sensible????

Dominic :D

[whammy]

Looks ok to me at a quick glance. ;)

Test it out and see what you get. If you're using SQL Server, I usually paste stuff like that into Query Analyzer to test it (after printing the actual value of your query to the page), if there are any problems. :D

[dominicall]

Hey whammy

OK - here's the answer - thought I'd let you know in case you ever need to use it...

Using Now() or Date() didn't seem to work - I did a test count and the count always returned zero.

So I tried using the getdate() SQL function and EUREKA - it worked - I don't really know exactly why but am assuming that it was SQL that need to get the date and not have it provided by IIS. Anyway - here's the full select statement...

"SELECT tbl_Offices.Office AS Office, tbl_Offices.OfficeID AS OfficeID, tblOffice.Email AS Email, tbl_Offices.FirstName AS FirstName, tbl_Offices.Surname AS Surname, tbl_Invoices.InvoicePaid AS InvoicePaid, tbl_invoices.InvoiceID AS InvoiceID, tbl_Invoices.InvoiceDate AS InvoiceDate, tbl_invoices.InvoiceAmount AS InvoiceAmount FROM tbl_Invoices LEFT OUTER JOIN tbl_Offices ON tbl_Invoices.OfficeID = tbl_Offices.OfficeID WHERE DateDiff(day,CONVERT(DATETIME,tbl_Invoices.InvoiceDate,103),CONVERT(DATETIME,getdate(),103)) > 30 AND tbl_Invoices.InvoicePaid = 0 ORDER BY InvoiceID"

Works a treat :D

Dominic

[whammy]

Duh... I know that too, but didn't think of it when replying. I often use getdate() as a default database value for a datetime field (to keep track of when the record was inserted).