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

Selecting records based on date

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
UNVME69
Asked:
UNVME69
  • 6
  • 4
1 Solution
 
UNVME69Author Commented:
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
 
UNVME69Author Commented:
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
 
UNVME69Author Commented:
Of course this query doesn't work, but gives you a better idea of what I mean
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
snoyes_jwCommented:
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
 
UNVME69Author Commented:
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
 
snoyes_jwCommented:
Not a problem re the 60 days, just change = to <= or >=, according to your need.

What's the error?
0
 
akshah123Commented:
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
 
UNVME69Author Commented:
SQL execution error#1064.

it says to check my mySQL version for syntax near the (datejoined))
0
 
snoyes_jwCommented:
Replace day with dayofmonth
0
 
UNVME69Author Commented:
Ok I got an error on that one also. Are those functions only for the new version of mySQL?
0
 
snoyes_jwCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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