Solved

Selecting records based on date

Posted on 2004-08-25
11
281 Views
Last Modified: 2010-03-19
Lets assume the current data in the DB is as follows, where each line represents a new record.

membesrs - Table
DateJoined - Field Name

---------------------------
2003-08-04 00:00:00
2003-06-02 00:00:00
2004-01-01 00:00:00
2003-07-04 00:00:00
---------------------------

I don't have the newest version of mySQL.

What I want to do is, I want to return records that the date is 60 days into the future, despite the year being in the past.

Here is psuedo code of what I want to do:

select customer_id, club_id from club_members
where year(datejoined) < year(now())
and day_difference(createdate(year(now()),month(datejoined),day(datejoined)),now()) = 60
0
Comment
Question by:UNVME69
  • 6
  • 4
11 Comments
 

Author Comment

by:UNVME69
ID: 11895080
I have came up with this query so far but I still cant figure out the create date function

select club_id from club_members
where TO_DAYS(year(datejoined) + month(now()) + day(now())) - TO_DAYS(now()) = 60
0
 

Author Comment

by:UNVME69
ID: 11895122
Update to the above query:

select club_id from club_members
where TO_DAYS(year(now()) + month(datejoined) + day(datejoined)) - TO_DAYS(now()) = 60
0
 

Author Comment

by:UNVME69
ID: 11895135
Of course this query doesn't work, but gives you a better idea of what I mean
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11895443
Any number of methods might work.  Here's one, which might give you troubles if they joined on a leap day:

... WHERE date_add(curdate(), interval 60 day) = concat_ws('-', year(curdate()), month(datejoined), day(datejoined));
0
 

Author Comment

by:UNVME69
ID: 11896353
I get an error when I attempt to use it.

The number doesn't have to be exactly 60 days. Matter of fact, I need to append the original post, it needs to be <= 60 days
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11896618
Not a problem re the 60 days, just change = to <= or >=, according to your need.

What's the error?
0
 
LVL 17

Expert Comment

by:akshah123
ID: 11896716
try

select club_id from club_members
where year(datejoined)<year(curdate()) AND (TO_DAYS(concat(year(now()),"-",month(datejoined),"-",day(datejoined))) - TO_DAYS(now()) <= 60)
0
 

Author Comment

by:UNVME69
ID: 11896727
SQL execution error#1064.

it says to check my mySQL version for syntax near the (datejoined))
0
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 125 total points
ID: 11896771
Replace day with dayofmonth
0
 

Author Comment

by:UNVME69
ID: 11896775
Ok I got an error on that one also. Are those functions only for the new version of mySQL?
0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11896889
day is an alias of dayofmonth, and was added in 4.1.1.  dayofmonth should be available in older versions, I don't know which.  Try date_format(datejoined, "%d") instead, or toss that whole concat_ws part and use date_add(datejoined, INTERVAL YEAR(curdate()) - YEAR(datejoined) YEAR)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now