[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

SQL Currdate how to look at yesterdays date

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()))
0
andrewgorrod
Asked:
andrewgorrod
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ContactDate = DATE_SUB(CURDATE(), INTERVAL '1' DAY)
 
0
 
Pratima PharandeCommented:
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=DATE_ADD(CURDATE(), INTERVAL -2 DAY)))

refer
http://wiki.nisi.ro/2011/01/mysql-select-today-yesterday-or-day-before-yesterday-records/
0
 
andrewgorrodAuthor Commented:
thanks for the reply pratima_mcs after pasting it in this is the error what am i missing
error screen shot
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you seem to have put a space before the ( ?
0
 
andrewgorrodAuthor Commented:
Sorry i have double checked and there is not space there I think it's the way the error is shown.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please clarify which version of MySQL you are using?
also, did you try my suggestion to use DATE_SUB instead of DATE_ADD, and to use quotes ('1') instead of just the number ...
0
 
Anthony PerkinsCommented:
>>Zones: MySQL Server, SQL Server 2005, MS SQL Server<<
You do realize the MySQL Server <> MS Server, right?  If you are not using MySQL than please do not post in that zone and request that it be removed from this thread.
0
 
Pratima PharandeCommented:
try simply like this

mysql>DATE_ADD(CURDATE(), INTERVAL -2 DAY)

or try

mysql>DATE_ADD(NOW(), INTERVAL -2 DAY)
0
 
andrewgorrodAuthor Commented:
Thanks that was the soluition i was look for.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now