MySQL IN BETWEEN with no condition

Question

I want to write a SQL query where if the datetime is not been assigned I want to replace a string which can select all date and time.

How Actually it works:

    SELECT * FROM `entry` WHERE `user` like 'TOM' or `user` like 'PHP'
 and `entry_datetime` between '2013-03-13 22:20:18' and '2013-03-13 22:20:26'

My Doubt: If the php do not get the value of the date from and to but I don't want to change the query by deleting the AND condition but I want to replace some thing in between date and time and still the query should run:

    SELECT * FROM `entry` WHERE `user` like 'TOM' or `user` like 'PHP' and 
`entry_datetime` between ' ' and ' '

I tried the following but its not working? can we write a query like this?

    SELECT * FROM `entry` WHERE `user` like 'TOM' or `user` like 'PHP' and 
`entry_datetime` between '%%' and '%%'

I just tries the above like because we use it in LIKE command so I tried that? Any advice will be much helpful.

I dont know the way I am trying is right or wrong but I can't change the Query id the date value is not assigned.

You are probably best off making the parameter nullable and doing an if statement and splitting your query. It will be much easier to read and not too much additional code. — nik0lai, Jun 11 '13 at 09:38
I would like to know is there any other way other than changing the query.. I am looking for a variable change i.e. only between values. — DonOfDen, Jun 11 '13 at 09:42
How are you passing in the date, via a parameter or is it hard coded? — nik0lai, Jun 11 '13 at 09:49

score 2 · Accepted Answer · answered Jun 11 '13 at 09:44

You can use MIN and MAX values for datetime data type.

... AND `entry_datetime` BETWEEN '1000-01-01 00:00:00' AND '9999-12-31 23:59:59'

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

score 1 · Answer 2 · answered Jun 11 '13 at 09:50

You could use the IFNULL function

IFNULL(mindate, '1000-01-01');

Problem with this is you would need to know your date range so you could add the minimum and maximum values correctly. This assumes you are passing in or creating a variable called mindate.

score 1 · Answer 3 · answered Jun 11 '13 at 09:57

1)

SELECT  *
FROM    `entry`
WHERE   `user` like 'TOM' or `user` like 'PHP'
and     `entry_datetime`
        between COALESCE('2013-03-13 22:20:18', '1000-01-01 00:00:00')
        and     COALESCE('2013-03-13 22:20:26', '9999-12-31 23:59:59');

2)

SELECT  *
FROM    `entry`
WHERE   `user` like 'TOM' or `user` like 'PHP'
and     `entry_datetime`
        between COALESCE(NULL, '1000-01-01 00:00:00')
        and     COALESCE(NULL, '9999-12-31 23:59:59');

If you pass NULL - optional date values will work as condtions

MySQL IN BETWEEN with no condition

3 Answers3

Linked