ODBC SQL Query CURRDATE -1

Post Reply
repgen
Posts: 4
Joined: Thu Apr 29, 2010 9:13 pm

ODBC SQL Query CURRDATE -1

Post by repgen » Thu Apr 28, 2011 4:03 pm

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()))

UKMonkeyMAn
Posts: 21
Joined: Tue May 19, 2009 7:54 pm

Re: ODBC SQL Query CURRDATE -1

Post by UKMonkeyMAn » Tue May 03, 2011 10:53 am

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()))

repgen
Posts: 4
Joined: Thu Apr 29, 2010 9:13 pm

Re: ODBC SQL Query CURRDATE -1

Post by repgen » Mon May 09, 2011 8:20 pm

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

Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests