?
Solved

Selecting records based on date

Posted on 2004-08-25
11
Medium Priority
?
310 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

749 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