more or less datetime in mysql

trscookie · 09-11-2009, 05:12 AM

Hello all,

I have a table that I am trying to select records from where the record is greater or equal to one date and equal or less than another date, I have found answers on BETWEEN two dates but not on less/more and equal to:

i.e:
what I am trying to acheive:

Code:

select user_id, start_dstamp, end_dstamp 
from users u 
where DATE(u.start_dstamp)<=DATE('2009-9-30') 
AND DATE(u.end_dstamp)>=DATE('2009-9-30') 
ORDER BY start_dstamp
;

however i always get no results found?

the data in the table:

Code:

select user_id, start_dstamp, end_dstamp from users;

user_id, start_dstamp,          end_dstamp
1  	 2009-08-11 00:00:00  	2009-08-11 23:59:00
4 	 2009-08-24 00:00:00 	2009-08-24 23:59:00
10 	 2009-08-28 00:00:00 	2009-08-28 23:59:00
14 	 2009-09-10 12:00:00 	2009-09-10 00:00:00
16 	 2009-09-28 00:00:00 	2009-09-30 23:59:00

start_dstamp and end_dstamp are both type datetime.

could someone show me what the correct syntax for this would be?

cheers in advance, trscookie.

jan61 · 09-11-2009, 10:12 AM

Moin,

I created a table like yours and checked the result, it works for me:

Code:

mysql> select user_id, start_dstamp, end_dstamp
    -> from users u
    -> where DATE(u.start_dstamp)<=DATE('2009-9-30')
    -> AND DATE(u.end_dstamp)>=DATE('2009-9-30')
    -> ORDER BY start_dstamp;
+---------+---------------------+---------------------+
| user_id | start_dstamp        | end_dstamp          |
+---------+---------------------+---------------------+
|      16 | 2009-09-28 00:00:00 | 2009-09-30 23:59:00 |
+---------+---------------------+---------------------+
1 row in set (0.02 sec)

That's the result I expected.

You can get the same result using BETWEEN:

Code:

mysql> select user_id, start_dstamp, end_dstamp from users where DATE('2009-09-30') between DATE(start_dstamp) AND DATE(end_dstamp) ORDER BY start_dstamp;
+---------+---------------------+---------------------+
| user_id | start_dstamp        | end_dstamp          |
+---------+---------------------+---------------------+
|      16 | 2009-09-28 00:00:00 | 2009-09-30 23:59:00 |
+---------+---------------------+---------------------+
1 row in set (0.00 sec)

Jan

trscookie · 09-11-2009, 10:31 AM

hummm, i remember seeing something similar when i tried to update via user_id. Ill drop the table and re-create it just in case its got its-self in a bit of a pickle, not quite sure what else to do :S, thanks anyway.

jlinkels · 09-11-2009, 08:59 PM

2009-09-30 translates to 2009-09-30 00:00:00 hence you cannot find any record which is both > 2009-09-30 and < 2009-09-30.

You could improve your query by search for > 2009-09-30 00:00 and < 2009-09-30 23:59

jlinkels