Re: [GENERAL] extracting date information?

On Tue, 19 May 1998, Kevin Heflin wrote:

> On Tue, 19 May 1998, Jose' Soares Da Silva wrote:
>
> > If you use "datetime" type instead of "date" and set datestyle = 'Postgres'
> > your data will be like: Sat Dec 12 00:00:00 1998 CET
> > instead of: 1998-12-12
>
> I actually stumbled across this after my initial post.
>
> Now that my dates are being retreived in this format:
> Sat Dec 12 00:00:00 1998 CET
>
> Is there a way to format this to leave off the "time" portion and maybe
> the "timezone" info ?

try this:

prova=> select c from a;
mydate
----------------------------
Sat Dec 12 00:00:00 1998 CET
(1 row)

prova=> select substring(cast(c as text) from 1 for 10)||substring(cast(c as
text) from 20 for 5) from a;
?column?
---------------
Sat Dec 12 1998
(1 row)

You may also create a function like this:

prova=> create function mydate(datetime) returns text
prova-> as 'select substring(cast($1 as text) from 1 for
10)||substring(cast($1 as text) from 20 for 5) from a;' language 'sql';
CREATE
prova=> select mydate(c) from a;
mydate
---------------
Sat Dec 12 1998
(1 row)
Jose'