How to check for a blank date value in Ingres

Question

I have an Ingres table with date field (data type ingresdate) which has a not null restriction. However blank i.e. empty values are allowed. How can you check for a blank value?

Of course testing for null values using ifnull() does not work - as per example below.

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

Yes I did, as you can see above ifnull( date_field, '01/01/2014' ) returns an empty value as well — Adrian, Aug 19 '14 at 14:42

score 3 · Answer 1 · answered Aug 19 '14 at 14:42

3

You can use

select * from test where date_field = ''

answered Aug 19 '14 at 14:42

PaulM

446
2
12

Thanks PaulM, I give you a +1 but what I was really looking for is the case expression from my own answer – Adrian Aug 19 '14 at 14:49
Sure. I hadn't realised you wanted the equivalent of an ifnull. The case is a good solution to that. – PaulM Aug 19 '14 at 14:52

score 1 · Answer 2 · answered Aug 19 '14 at 14:57

1

Just a design suggestion - allow NULL values in the fields and insert NULL instead of empty string.

answered Aug 19 '14 at 14:57

Bulat

6,869
1
29
52

score 0 · Accepted Answer · answered Aug 19 '14 at 14:48

Actually, thanks to PaulM, I figured it out:

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

score 0 · Answer 4 · answered Aug 23 '14 at 14:53

0

Whenever a date field is not nullable (and I prefer it that way) I use the start of the Unix epoch date/time (1970-01-01T00:00:00Z ISO 8601) to indicate the field is not yet initialized.

answered Aug 23 '14 at 14:53

DejanLekic

18,787
4
46
77

score -1 · Answer 5 · edited Aug 19 '14 at 19:53

-1

You can convert the field to characters and count them.

SELECT * FROM MyTable WHERE LENGTH(c(date_field)) > 0

edited Aug 19 '14 at 19:53

Alpha

7,586
8
59
92

answered Aug 19 '14 at 18:55

Rene Contreras

1

All questions and answers on StackOverflow have to be in English. Please do not post anything in an other language. – Sumurai8 Aug 19 '14 at 19:30

How to check for a blank date value in Ingres

5 Answers5