Re: [GENERAL] Typecasting datetype as date. How do I cope with NULLs?

>>>>> "SR" == Stuart Rison <stuart(at)ludwig(dot)ucl(dot)ac(dot)uk> writes:

SR> Dear All,
SR> For those of you who don't want to wade through the details, here's the
SR> question: "How do I get the date portion of a datetime field for ALL
SR> ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
SR> Typecasting a datetime NULL as date generates an error)"

. . .

SR> I find myself having to do two selects to get all the people in the table.
SR> i.e.:

SR> patients=> SELECT surname,firstname,othernames,dob::date FROM patients
SR> WHERE dob
SR> IS NOT NULL;
SR> surname|firstname|othernames | date
SR> -------+---------+--------------------+----------
SR> Goose |Mother |Lay Golden Eggs |11-01-1923
SR> One |Un |Uno Ein |11-11-1111
SR> Light |Dee |Full |22-01-1933
SR> (3 rows)

SR> patients=> SELECT surname,firstname,othernames FROM patients WHERE dob IS NULL;
SR> surname|firstname|othernames
SR> -------+---------+----------
SR> Rison |Stuart |
SR> Rison |This |Pal
SR> Rison |Mark |
SR> (3 rows)

SR> My question is, how do I get surname,firstname,othername and the date
SR> portion of ALL people in table people regardless of whether the entry has
SR> an actual dob or a NULL dob.

There are, on my mind, at least two answers. For experience I use
small table 'create table a( dt datetime, i int)'. Hera are data in
this table (one row has NULL as dt value):

tolik=> select * from a;
dt | i
----------------------------+--
Thu Nov 26 16:35:23 1998 MSK| 1
Wed Nov 25 00:00:00 1998 MSK| 2
Fri Nov 27 00:00:00 1998 MSK| 3
|10

First use 'union':
-----------------------------------------------
select dt::date, i from a where dt is not null
union
select NULL, i from a where dt is null;
date| i
----------+--
11-25-1998| 2
11-26-1998| 1
11-27-1998| 3
|10
(4 rows)
-----------------------------------------------

Second, try use date_trunc('day', dt) instead date_part:
--------------------------------------------------------------
tolik=> select date_trunc('day', dt), i from a;
date_trunc | i
----------------------------+--
Thu Nov 26 00:00:00 1998 MSK| 1
Wed Nov 25 00:00:00 1998 MSK| 2
Fri Nov 27 00:00:00 1998 MSK| 3
|10
(4 rows)

--------------------------------------------------------------

Regards!

--
Anatoly K. Lasareff Email: tolik(at)icomm(dot)ru
Senior programmer