invert date in BETWEEN with MySQL


Someday i want to check my software and i get some error with my query when selecting data transaction in range date ‘2008-12-05’ and  ‘2008-12-01’ (descending)… it’s very surprising when the result is empy (zero).

But when selecting when selecting data transaction in range date ‘2008-12-01’ and  ‘2008-12-05’ (ascending) i got the result…

So, i want to practice little experiment about this case.

I got employee data in table

CREATE TABLE `employee1` (
`ID` int(2) NOT NULL auto_increment,
`EMPNAME` varchar(30) default NULL,
`ATTENDENCE` varchar(10) default NULL,
`DAT` date NOT NULL default ‘0000-00-00′,
PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

insert  into `employee1`(`ID`,`EMPNAME`,`ATTENDENCE`,`DAT`) values (1,’Girish’,’P’,’2008-12-01′),(2,’Komal’,’A’,’2008-12-02′),(3,’Mahendra’,’P’,’2008-12-03′),(4,’Sandeep’,’A’,’2008-12-04′),(5,’Amit’,’P’,’2008-12-05′);

Query 1:

select EMPNAME FROM employee1 WHERE DAT BETWEEN ‘2008-12-01’ and ‘2008-12-05’

Query with result’s empty:

select EMPNAME FROM employee1 WHERE DAT BETWEEN ‘2008-12-05’ and  ‘2008-12-01’

The solution when you want to selecting data with range of date, you can use this query

select EMPNAME
FROM employee1
WHERE DAT <= ‘2008-12-05’ and DAT >= ‘2008-12-01’

or this query

select EMPNAME
FROM employee1
WHERE DAT >= ‘2008-12-01’ and DAT <= ‘2008-12-05’

About blackphp

All about programming php, ajax, jquery, mysql, etc, tips n trick. Now, i'm concern in php programming with codeigniter framework n also training my design skill with photoshop or corel... wish me luck! ('o')

Posted on August 1, 2010, in mYsQL and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: