Page 1 of 1

ODBC SQL Query CURRDATE -1

Posted: Thu Apr 28, 2011 4:03 pm
by repgen
I have this SQL selection as below but wish to look at ContactDate=CURDATE but for yesterday eg today -1. Having tried many options I still seem unable to get it to look at yesterdays date. Any help please

SELECT MK_01_ContactHistoryRecords.ActivityCode, MK_01_ContactHistoryRecords.ContactDate, MK_01_ContactHistoryRecords.CustomerMagicNo, MK_01_VehicleRecords.RegistrationNumber, MK_01_VehicleRecords.NextServiceDate
FROM MK_01_ContactHistoryRecords MK_01_ContactHistoryRecords, MK_01_VehicleRecords MK_01_VehicleRecords
WHERE MK_01_ContactHistoryRecords.VehicleLinkMagic = MK_01_VehicleRecords.VehicleNumber AND ((MK_01_ContactHistoryRecords.ActivityCode='T31') AND (MK_01_ContactHistoryRecords.ContactDate=CURDATE()))

Re: ODBC SQL Query CURRDATE -1

Posted: Tue May 03, 2011 10:53 am
by UKMonkeyMAn
Hi repgen.

To get yesterdays data you would need to change the last selection criteria to:

(MK_01_ContactHistoryRecords.ContactDate = TIMESTAMPADD(SQL_TSI_DAY, -1, CURDATE()))

Re: ODBC SQL Query CURRDATE -1

Posted: Mon May 09, 2011 8:20 pm
by repgen
UKMonkeyMAn
Thanks for your help. With a bit of help and tweaking I ended up with: ((TIMESTAMPDIFF(SQL_TSI_DAY,MK_01_ContactHistoryRecords.ContactDate,CURDATE())=1)
Thanks