Jump to content


Photo

MySQL Date functions


  • Please log in to reply
4 replies to this topic

#1 rhysmeister

rhysmeister
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts
  • LocationEngland

Posted 29 October 2003 - 03:46 PM

I am trying to grab a field in a database where a date field is between the current date and within 60 / 45 / 30 / 15 days time(selected by a drop down box). Here is my sql

SELECT domainname FROM domainname WHERE expirydate BETWEEN CURDATE() and CURDATE()+$selecteddays

I have typed the same sql into the MySQL console and it accepts it without error, but returns no results, but I know there are entries the query should pick out that are present in the table. Any ideas people? Many thanks!

#2 radox

radox
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts

Posted 29 October 2003 - 04:02 PM

your sytanx doesn\'t even make sense. Today+15?!?!...that day doesn\'t even exist yet! Another thing, the earllier date has to come first. Try:

SELECT * FROM table
WHERE (Date BETWEEN CURDATE()-\'$selecteddays\' and CURDATE())


i\'m pretty sure you meant 15/45/60 days back...right?

#3 pauper_i

pauper_i
  • Members
  • PipPipPip
  • Advanced Member
  • 59 posts
  • LocationPhoenix, AZ, USA

Posted 29 October 2003 - 07:02 PM

Take a look at the DATE_ADD(date,INTERVAL expr type) and DATE_SUB(date,INTERVAL expr type) functions:
http://www.mysql.com..._functions.html
Genius is one percent inspiration, and ninety-nine percent perspiration. [br]Thomas Alva Edison

#4 rhysmeister

rhysmeister
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts
  • LocationEngland

Posted 03 November 2003 - 12:31 PM

Just for the record...

The above query was actually taken from an Access DB and worked fine (except CURDATE was DATE), obviously not standard SQL I guess. Thanks.

#5 rhysmeister

rhysmeister
  • Members
  • PipPipPip
  • Advanced Member
  • 51 posts
  • LocationEngland

Posted 03 November 2003 - 02:08 PM

Got the query working...

SELECT domainname FROM domainname WHERE expirydate           BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL $selecteddays DAY)





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users