List:General Discussion« Previous MessageNext Message »
From:hsv Date:October 22 2013 8:23pm
Subject:Re: Date comparison help
View as plain text  
>>>> 2013/10/22 12:20 -0400,  >>>>
I recently upgraded a local MySQL installation to 5.5.32 and am trying to figure out why
the following query won't work as expected anymore. I'm just trying to compare a set of
dates to NOW() but since the upgrade, these don't seem to work as expected.

SELECT 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY), 
NOW(), 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY)<NOW()

For instance, when I run it on my system, I get 1 for the third column even though
comparing the two by eye it should be false.
<<<<<<<<
Well, show us all three columns....

And with 5.5.8 I get the same third column as you. Has it worked?

And I found that changed to

SELECT 
DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY) AS A, 
NOW(), 
CAST(DATE_ADD(STR_TO_DATE('2013-350-00:00:00','%Y-%j-%H:%i:%S'),INTERVAL 2 DAY) AS
DATETIME)<NOW() AS B

it works as hoped for--and it seems a bug to me, but probably an old one. It seems to me
that the outcome of DATE_ADD is DATE, not DATETIME, and the comparison is numeric, with
the six trailing 0s dropped. Quote about STR_TO_DATE:
"It takes a string str and a format string format.
<file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/functions.html#function_str-to-date>STR_TO_DATE()
returns a
<file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetime>DATETIME
value if the format string contains both date and time parts, or a
<file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#datetime>DATE
or
<file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/data-types.html#time>TIME
value if the string contains only date or time parts. "
How really does it decide which type to return? It is wrong if the decision is based
whether all the hour, minute, and second are 0 or not.  

Thread
Date comparison helpMichael Stroh22 Oct
  • Re: Date comparison helpkitlenv22 Oct
    • Re: Date comparison helpMichael Stroh23 Oct
Re: Date comparison helphsv23 Oct