mysql - 如何从MySQL中具有日期计算的查询结果中创建表？

我想从qry结果创建一个表。以下qry运行正常：

create table x select
date_format(TERMINATION_DT,'%Y-%m-%d') as Term_Date
, date_format(BIRTH_DT,'%Y-%m-%d') as DOB
from dashboard_04102014
where status = 'withdrawn';

有些termination_dt值是空的''。 where status = 'withdrawn'过滤出空值。但是，当我尝试使用以下qry执行日期计算时，出现错误消息：

create table x select
date_format(TERMINATION_DT,'%Y-%m-%d') as Term_Date
, round(datediff(date_format(TERMINATION_DT,'%Y-%m-%d'), date_format(BIRTH_DT,'%Y-%m-%d'))/365,0) as Age
from dashboard_04102014
where status = 'withdrawn';

这是错误消息：

incorrect datetime value" '0000-00-00'

我猜想那些空的termination_dt值会转换为0000-00-00从而抛出datediff吗？但是这些值首先被where子句过滤掉了。

另外，我可以很好地运行select部分，它返回我想要的结果。但是，当我想从中创建表时，我收到了带有第二个qry的错误消息。为什么我可以用第一个qry创建表但不能用第二个？

谢谢！

添加原始表带：

CREATE TABLE dashboard_04102014
   (`status` varchar(9), `termination_dt` int, `birth_dt` int);

INSERT INTO Table1
(`status`, `termination_dt`, `birth_dt`)
VALUES
('Active', 0, 19560329),
('Withdrawn', 20070208, 19690131),
('Withdrawn', 20090702, 19441219),
('Active', 0, 19520912),
('Withdrawn', 20130730, 19480404);

最佳答案

如果您没有从日期列中选择NULL或empty条目，则

drop table if exists x;

create table x
select
  cast( date_format( BIRTH_DT,'%Y-%m-%d' ) as date ) as BIRTH_DT
  , cast( date_format( TERMINATION_DT,'%Y-%m-%d' ) as date ) as Term_Date
  , cast( round( datediff( date_format( TERMINATION_DT, '%Y-%m-%d' ),
                     date_format( BIRTH_DT, '%Y-%m-%d' )
                 ) / 365, 0 ) as decimal( 6, 2 ) ) as Age
from dashboard_04102014
where
      status = 'withdrawn'
      -- un comment following line if required
      -- AND ( BIRTH_DT is not null and BIRTH_DT = '' and BIRTH != 0 )
  and ( TERMINATION_DT is not null
        and TERMINATION_DT != ''
        and TERMINATION_DT != 0 )
;

结果：

desc x;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| BIRTH_DT  | date         | YES  |     | NULL    |       |
| Term_Date | date         | YES  |     | NULL    |       |
| Age       | decimal(6,2) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

select * from x;
+------------+------------+-------+
| BIRTH_DT   | Term_Date  | Age   |
+------------+------------+-------+
| 1992-07-31 | 2050-07-31 | 58.00 |
| 1971-02-24 | 2029-02-24 | 58.00 |
+------------+------------+-------+

演示1 @ MySQL 5.5.32 Fiddle
演示2 @ MySQL 5.5.32 Fiddle