From: Peter Brawley
Date: February 13 2012 4:42pm
Subject: Re: weird difference in workbench and CLI query
List-Archive: http://lists.mysql.com/mysql/226789
Message-Id: <4F393D6D.1010203@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2/13/2012 10:18 AM, James W. McNeely wrote:
> When I run this query in workbench:
> select c.acc_number 'Accession Number', e.DateExam 'MBI Exam Date', s.ExamDate 'SJH Exam Date'
> from chestcon_log c
> left join exams e on CONCAT("000",c.acc_number) = e.LastWordAccNum
> left join sjhreports_ s on c.acc_number = s.AccessionNumber
> WHERE
> c.timestamp_exam<= CAST(DATE_ADD(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL '-1' DAY) AS DATETIME)
> AND c.timestamp_exam>= CAST(DATE_ADD(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL '-14' DAY) AS DATETIME)
>
> I get this:
> 7330565	NULL	 2012-02-01
> 6604419	 2011-01-25	NULL
>
> but when I run the same query in a shell script, on a Linux box or OS X, I get this:
> 7330565	NULL	2012-02-01
> 6604419	NULL	NULL
What are the MySQL versions and sql_mode settings on the two boxes?

PB

-----

>
> I also tried echo this and piping it into MySQL with no shell script. Same result. Why does the date eval to null in CLI?