?
Solved

MySQL query question: today +/- 30 days

Posted on 2003-03-26
8
Medium Priority
?
449 Views
Last Modified: 2012-06-27
I have a table with the following information:

CREATE TABLE information (
  ID smallint(6) NOT NULL auto_increment,
  Date text,
  DEVELOPMENT text,
  ALPHA text,
  BETA text,
  SPECIAL text,
  NOTES text,
  PRIMARY KEY  (ID),
  KEY information_ID (ID)
) TYPE=MyISAM;

What I need to do is create a query that will return all records that fall within the range of today plus 30 days and minus 30 days.

If that isnt possible how would i structure just the "today plus 30"? any help is appreciated. Thx
0
Comment
Question by:nriddock
[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
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 8211612
well first you are storing Date as text, store it as DATETIME and i wouldn't call the column Date as it's typically a reserved word.

after you do that, then you could do something like:

SELECT * FROM information
   WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30
   OR TO_DAYS(date_col) - TO_DAYS(NOW()) < = 30
0
 
LVL 15

Expert Comment

by:VGR
ID: 8211653
well, better use ABS(delta)<=30

and it's an AND above or you'll select too old or too far away dates

example : date_col<now()-30j
thus now()-date_col>30 but date_col-now<-30 so it's <=30 so it'll be seected
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 8211699
right, sorry, it should be AND ;)
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:nriddock
ID: 8211984
ok so i changed my date field to 'EndDate' and changed the type to DATE (dont need the Date and Time displayed with DATETIME)

what would that query look like? thx
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 8212406
SELECT * FROM information
  WHERE TO_DAYS(NOW()) - TO_DAYS(EndDate) <= 30
  AND TO_DAYS(EndDate) - TO_DAYS(NOW()) <= 30
0
 

Author Comment

by:nriddock
ID: 8212519
EXCELLENT...That works perfectly...one last question...how/where would i add "previous 30 days"  to the above query so it would display Prev/Current/Future.

Thx
0
 
LVL 18

Accepted Solution

by:
bobbit31 earned 1600 total points
ID: 8213393
something like this should work:

SELECT *,
IF(EndDate < NOW(), "Prev", IF(EndDate > Now(), "Future", "Current")) as status
FROM information
 WHERE TO_DAYS(NOW()) - TO_DAYS(EndDate) <= 30
 AND TO_DAYS(EndDate) - TO_DAYS(NOW()) <= 30

status will now contain Prev/Current/Future depending on EndDate
0
 

Author Comment

by:nriddock
ID: 8217857
works perfectly....thx for your help
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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…
Suggested Courses

765 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