Mysql date time stuff

[derrick24]

I'm trying to find out how many updates where done during the month of April, thing is I'm also getting 2007 entries and I don't think this is correct. 

 

SELECT * FROM ads WHERE (flag = 0) AND (updated > 2008-03-29 AND updated < 2008-05-01) order by updated

[PFMaBiSmAd]

Could you post your table definition and one or two of the 2007 values exactly how they appear in the database table.

[derrick24]

CREATE TABLE  `tgv`.`gv_ads` (
  `id` int(10) NOT NULL auto_increment,
  `flag` int(1) default '0',
  `counter` int(10) default '0',
  `cat` varchar(255) default NULL,
  `title` varchar(255) default NULL,
  `address` varchar(255) default NULL,
  `tel` varchar(45) default NULL,
  `fax` varchar(45) default NULL,
  `cell` varchar(45) default NULL,
  `email` varchar(100) default NULL,
  `url` varchar(255) default NULL,
  `description` text,
  `image` varchar(255) default NULL,
  `error` int(11) unsigned default '0',
  `area` varchar(45) default NULL,
  `updated` date default NULL,
  `tel_a` varchar(45) default NULL,
  `tel_b` varchar(45) default NULL,
  `tel_c` varchar(45) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4745 DEFAULT CHARSET=latin1;

 

Hope that helps, and Ive attached an extract from the database.

 

[attachment deleted by admin]

[PFMaBiSmAd]

I created a test table and the posted query actually returned no rows for me and the problem is you need to put the date values inside of single quotes -

 

SELECT * FROM ads WHERE (flag = 0) AND (updated > '2008-03-29' AND updated < '2008-05-01') order by updated

 

Without the quotes, mysql is apparently performing subtraction: 2008 - 03 - 29 = 1976 and 2008 - 05 - 01 = 2002, resulting in some indeterminate comparison (updated > 1976 AND updated < 2002)

[PFMaBiSmAd]

When selecting a specific month, to keep from needing to calculate the range of dates, I generally use something like the following -

 

SELECT * FROM ads WHERE (flag = 0) AND MONTH(updated) = 4 AND YEAR(updated) = YEAR(CURDATE()) order by updated

 

You could also do the following if you wanted to list more than one specific month -

 

SELECT * FROM ads WHERE (flag = 0) AND MONTH(updated) IN(4,5,6) AND YEAR(updated) = YEAR(CURDATE()) order by updated

 

[derrick24]

Thank you very much, that information is extremely helpful! Much appreciated.