NULLIF truncating date values only when in a UNION ALL Statement in MySql

Asked Nov 05 '16 at 19:38

Active Nov 05 '16 at 20:27

Viewed 75 times

I am using a statement similar to this to union the results to two similar tables.

select sale_time, nullif(sale_time, '0000-00-00') as 'nullif_sale_time'
from tblCompletedSales
union all
select sale_time, nullif(sale_time, '0000-00-00') as 'nullif_sale_time'
from tblOpenSales;

sale_time is a timestamp field on both tables. Instead of getting the full date, in nullif_sale_time, I get a truncated version. In other words, if sale_time is '2015-08-12 09:33:46' nullif_sale_time will be '2015-0'. This is true for records from both tblCompletedSales and tblOpenSales.

Example:

sale_time             nullif_sale_time
2015-06-15 10:44:44   2015-0

If I run either statement without the union all they both work as expected. This has worked as expected until today. This is happening on my local machine as well as my remote server.

What could cause this? Is there something I am missing?

Edit:

Here is a fiddle that shows the same thing.

edited Nov 05 '16 at 20:27

asked Nov 05 '16 at 19:38

bconrad

1

What happens if you use `date('0000-00-00')` as the second argument? – Gordon Linoff Nov 05 '16 at 19:44
Using date('0000-00-00') makes it work as expected. Is there a reason that my old way would have been working until today? – bconrad Nov 05 '16 at 20:04
Have you updated MySQL / it's settings? Try SET SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; – slaakso Nov 05 '16 at 20:07

NULLIF truncating date values only when in a UNION ALL Statement in MySql

0 Answers0