如何在Ingres中检查空日期值

Question

我有一个带有日期字段(数据类型为ingresdate)的Ingres表，它有一个null限制。但是，允许空值，即为空值。如何检查空白值？

当然，使用ifnull()测试空值不起作用-如下例所示。

INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres SPARC SOLARIS Version II 9.2.3 login
continue
create table test ( id integer not null, date_field ingresdate not null with default )\g
insert into test (id, date_field) values ( 1, '' )\g
insert into test (id, date_field) values ( 2, '31/12/2014' )\g
continue
(1 row)
continue
select id, date_field, ifnull( date_field, '01/01/2014' ) as test_field from test\g
(1 row)
continue
+-----------------------------------------------------------------+
¦id           ¦date_field               ¦test_field               ¦
+-------------+-------------------------+-------------------------¦
¦            1¦                         ¦                         ¦
¦            2¦31/12/14                 ¦31/12/14                 ¦
+-----------------------------------------------------------------+
(2 rows)
continue
\q
Your SQL statement(s) have been committed.
Ingres Version II 9.2.3 logout

Answer 1

实际上，多亏了PaulM，我才明白了这一点：

INGRES TERMINAL MONITOR Copyright 2008 Ingres Corporation
Ingres SPARC SOLARIS Version II 9.2.3 login

select id, date_field, 
case when date_field = '' then '01/01/2014' else date_field end as test_field 
from test
+-----------------------------------------------------------------+
¦id           ¦date_field               ¦test_field               ¦
+-------------+-------------------------+-------------------------¦
¦            1¦                         ¦01/01/14                 ¦
¦            2¦31/12/14                 ¦31/12/14                 ¦
+-----------------------------------------------------------------+
(2 rows)

Ingres Version II 9.2.3 logout

Answer 2

您可以使用

select * from test where date_field = ''

Answer 3

只是一个设计建议--允许字段中的空值，并插入空字符串而不是空字符串。