Solved

Selecting records based on date

Posted on 2004-08-25
11
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 48
A query works with MySQL 5.6, mariadb 10.1 but fail on MySQL 5.7 6 57
Combining Queries 7 40
MYSQL responding very slow 3 48
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

735 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