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

>>>>> "SR" == Stuart Rison <stuart@ludwig.ucl.ac.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@icomm.ru
Senior programmer