• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

update query

Hi,

I want to update the AdsStatus in the database based on the length of the stored AdsDate .

updated AdsStatus where AdsDate is between 30 days period.

      Update ADS
      set AdsStatus = 5
      where AdsDate > (CURRENT_DATE()- 30) and
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
lulu50
Asked:
lulu50
  • 4
  • 3
  • 2
  • +2
1 Solution
 
mbizupCommented:
Do you mean where the date is between 30 days ago and 30 days from now?


Update ADS
      set AdsStatus = 5
      where AdsDate BETWEEN (CURRENT_DATE()- 30)  AND (CURRENT_DATE() +30) AND 
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Open in new window


For Access:

Update ADS
      set AdsStatus = 5
      where AdsDate BETWEEN (DATE()- 30)  AND (DATE() +30) AND 
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Open in new window

0
 
lulu50Author Commented:
I want to calculate the date difference between

today's date and AdsDate (than say update the record when the difference is more than 30 days)
0
 
Muhammad KhanManager, ITCommented:
Update ADS
      set AdsStatus = 5
      where CURRENT_DATE()-AdsDate > 30 and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
CluskittCommented:
I would just replace CURRENT_DATE()-AdsDate with DATEDIFF(day,AdsDate,CURRENT_DATE()). SQL sometimes does weird things with direct date calculations.
0
 
sachinpatil10dCommented:
Try this

Update ADS
      set AdsStatus = 5
      where datediff(day,AdsDate,getdate()) > 30 AND 
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Open in new window

0
 
mbizupCommented:
This will work for a difference of more than thirty days in either direction (before or after)
Update ADS
      set AdsStatus = 5
      where (CURRENT_DATE()-AdsDate > 30  OR AdsDate - CURRENT_DATE() > 30) and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Open in new window


In Access:

Update ADS
      set AdsStatus = 5
      where (DATE()-AdsDate > 30  OR AdsDate - DATE() > 30) and (MainCategory = 4 or MainCategory = 5 or MainCategory = 6) 

Open in new window

0
 
lulu50Author Commented:
Update ADS
      set AdsStatus = 5
      where datediff(day,AdsDate,getdate()) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  



Error Executing Database Query.  
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'getdate()) > 30 AND (MainCategory = 4 or MainCategory = 5 or MainCategory =' at line 3  
0
 
CluskittCommented:
MySQL uses NOW() instead of GETDATE()
0
 
lulu50Author Commented:
Update ADS
      set AdsStatus = 5
      where datediff(day,AdsDate,NOW()) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  



Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW()) > 30 AND (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)' at line 3
0
 
CluskittCommented:
MySQL datediff is also different. Try this:
Update ADS
      set AdsStatus = 5
      where datediff(AdsDate,NOW()) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  
0
 
Muhammad KhanManager, ITCommented:
Update ADS
      set AdsStatus = 5
      where datediff(NOW(),AdsDate) > 30 AND
   (MainCategory = 4 or MainCategory = 5 or MainCategory = 6)  
0
 
lulu50Author Commented:
Thank you
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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