I have a table named hr events where, in one field, it outputs when a certain employee went on maternity leave;
On the return_date field, it displays the date when the employee got back from the maternity leave.
If the employee has not come back yet, we set a default value, as seen on CREATE statement below: CREATE (return_date DATE NOT NULL DEFAULT '0000-00-00',);
There is one last field on this report where it is supposed to show the employee's final return date to her activities: (c.return_date ,'%d/%m/%Y') AS c_return_date. As an SQL beginner, i'm struggling with this: whenever we have a (default 0000-00-00) value, c.return_date brings some sort of aleatory date, like: e.g 12/01/1999 , tho' it's a NULL field.
I've been trying to treat this with ISNULL(c.return_date, '') or ISNULL(c.return_date, 0) but it just doesn't work. In the above scenario, this last field should be blank or anything but this '12/01/1999'.
As for PHP, and that's why I'm editing this topic, I think there could be a way out of this by addressing this issue inside the php script:
<?php
$retorno = (!empty($row['c_return_date'])) ? $row['c_return_date'] : $row['a_return_date'];
$data = explode('-', $retorno);
echo date("d/m/Y", mktime(0, 0, 0, $data[1], $data[2] + 1, $data[0]));
?>
I'm currently trying to figure out (actually, more figuring out how does this script works) how to validate this script to, in case of a return date like 0000-00-00 (which means the employee has not come back yet to her activities), not display these sort of output like 12/01/1999.
After all, it was a php script issue.
<?php
$retorno = (!empty($row['c_return_date'])) ? $row['c_return_date'] : $row['a_return_date'];
$data = explode('-', $retorn);
echo date("d/m/Y", mktime(0, 0, 0, $date[1], $date[2] + 1, $date[0]));
?>
I just needed to put a contitional statement on the script above, to prevent c_return_datefrom displaying anything in case a_return_date is 0000-00-00. Take a look at the if / else clauses:
<?php
if ($row['a_return_date'] != "0000-00-00"){
$return = (!empty($row['c_return_date'])) ? $row['c_return_date'] : $row['a_return_date'];
$data = explode('-', $return);
echo date("d/m/Y", mktime(0, 0, 0, $date[1], $date[2] + 1, $date[0]));
} else $row['a_return_date_br'] = "-";
?>
Thank you all. Hope it helps somebody else.
I don't think it's a random date. It's just a date represented by 0000-00-00 in MySQL.
I would use NULL:
create table hr_events (return_date date default null);
That way you could query list this:
select * from hr_events where return_date is null;
To get all hr_events rows where return_date has not been set.
I recommend allowing your date field to be nullable:
CREATE TABLE yourTable (return_date DATE, ...)
Then, allow null to semantically mean that the employee has not yet returned from maternity leave. You may use COALESCE to replace that null value with some default date:
SELECT COALESCE(return_date, '1999-12-01') AS return_date
FROM yourTable;