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

MySQL query question: today +/- 30 days

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
nriddock
Asked:
nriddock
  • 4
  • 3
1 Solution
 
bobbit31Commented:
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
 
VGRCommented:
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
 
bobbit31Commented:
right, sorry, it should be AND ;)
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nriddockAuthor Commented:
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
 
bobbit31Commented:
SELECT * FROM information
  WHERE TO_DAYS(NOW()) - TO_DAYS(EndDate) <= 30
  AND TO_DAYS(EndDate) - TO_DAYS(NOW()) <= 30
0
 
nriddockAuthor Commented:
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
 
bobbit31Commented:
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
 
nriddockAuthor Commented:
works perfectly....thx for your help
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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