Solved

Selecting records based on date

Posted on 2004-08-25
11
293 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
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.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL: Updating SubQuery Match Faster 9 53
unable to insert record into a table 2 37
Incorrect definition of table mysql.proc 7 49
MySQL Backup Strategy 15 44
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 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