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

At 13:12 +0200 on 27/11/98, Stuart Rison wrote:

>
> Again, this solves one part of my problem (i.e. the query functions even if
> I datetime is NULL) but what I'm looking for is something that ONLY shows
> the date portion of a datetime (e.g. Thu Nov 26 16:35:23 1998 MSK becomes
> 26-11-1998) so the date_trunc masking is not quite what I'm looking for.
>
> Would it be possible to do a select datetime and then output only the
> dateday, datemonth and dateyear using an RE??
>
> All the same, thanks for your suggestion Anatoly, UNION's the winner so
>far ;)

Well, first I'd like to say that not converting null datetimes is
definitely a bug and should be placed in the ToDo list for the next version.

That said, I tried several conversions, using the following table:

testing=> select * from test1;
f1 |dt
------------+----------------------------
This year |Fri Jan 02 00:00:00 1998 IST
Next year |Mon Mar 15 00:00:00 1999 IST
Nothing |
Last year |Mon May 12 00:00:00 1997 IDT
More nothing|
(5 rows)

The one that finally worked is the following:

testing=> select f1, date( abstime( dt ) ) from test1;
f1 | date
------------+----------
This year |01-02-1998
Next year |03-15-1999
Nothing |
Last year |05-12-1997
More nothing|
(5 rows)

That is, convert to abstime, and then to date...

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma