CODEWITHSUNDEEP
mysqldatedatetime
Jerry2
Jerry2

Reputation: 3085

Compare dates or dates with time, depending if the time is set

I have a datetime field. If the user in form enters only date, the time will be set as 00:00:00 of course, that is the way it is supposed to be.

But in mysql I need to compare this field with now() or with curdate(). If the time is set and is not 00:00:00 I need to compare only dates, if the time is other value I need to compare it with now().

For example, if I use in mysql date>=now() and the user set only date like 2013/12/22 then the comparation will be false on 12/22 which I do not want. I would like that comparation would be false on 12/23 then, the entered date is still valid on the same date.

Now if use date>=curdate() then the time will be ignored if somebody set time.

One solution would be to check if time is 00:00:00 and then somehow change it to 23:59:59 for comparation.

So bottom line is, if there is 00:00:00 as time in datetime field, user had obviously set only date so I need to compare date to curdate(). If time is set to any other value I need to compare date to now().

Is that doable?

Upvotes: 0

Views: 33

Answers (1)

krokodilko
krokodilko

Reputation: 36107

I would try this condition:

dt >= now()
OR 
( 
  time( dt ) = '00:00:00'
  AND 
  dt >= date( now() ) 
);

dt >= now() ==> cheks if a datetime dt is greather than now().
time( dt ) = '00:00:00' ==> checks if a time part is 00:00:00
date( now() ) ==> truncates a time part from now(), leaves only a date part.

See a demo ==> http://www.sqlfiddle.com/#!2/8609c/6

Upvotes: 1

Related Questions