Lists: | pgsql-admin |
---|
From: | Chris Pesko <cpesko(at)organic(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | help on to_date conversion please.... |
Date: | 2002-04-03 23:13:29 |
Message-ID: | 4.3.2.7.2.20020403150804.0464e440@mailhost.organic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
I am converting an 8 digit number to a date. I don't see a postgres
function to do it direct, but two functions seems like I'm on the right
track, to_char and to_date. Individually I get them to work, but when I
combine them I get the wrong answer. see below. Can anyone help in
getting the right date from an 8 digit number input formatted as yyyymmdd?
hwds=# select to_char(20020101, '99999999');
to_char
-----------
20020101 CORRECT.
(1 row)
hwds=# select to_date('20020101','yyyymmdd');
to_date
------------
2002-01-01 CORRECT.
(1 row)
hwds=# select to_date(to_char(20020101, '99999999'),'yyyymmdd');
to_date
------------
2003-08-12 WHY DOES IT GIVE ME DIFFERENT ANSWER WHEN I COMBINE THE
FUNCTIONS?
(1 row)
From: | Chris Pesko <cpesko(at)organic(dot)com> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: help on to_date conversion please.... |
Date: | 2002-04-03 23:42:32 |
Message-ID: | 4.3.2.7.2.20020403153945.00bb2030@mailhost.organic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Never mind. Just figured it out. Need to grab the substring from 2 for 8
from the to_char result because it holds character number 1 reserv ed for +
or - signs. + is default or white space. I think that is the reason. Any
other input?????
hwds=# select to_date(substring(to_char(20020101, '99999999') from 2 for
8), 'yyyymmdd');
to_date
------------
2002-01-01
(1 row)
At 03:13 PM 4/3/02 -0800, Chris Pesko wrote:
>I am converting an 8 digit number to a date. I don't see a postgres
>function to do it direct, but two functions seems like I'm on the right
>track, to_char and to_date. Individually I get them to work, but when I
>combine them I get the wrong answer. see below. Can anyone help in
>getting the right date from an 8 digit number input formatted as yyyymmdd?
>
>
>hwds=# select to_char(20020101, '99999999');
> to_char
>-----------
> 20020101 CORRECT.
>(1 row)
>
>hwds=# select to_date('20020101','yyyymmdd');
> to_date
>------------
> 2002-01-01 CORRECT.
>(1 row)
>
>hwds=# select to_date(to_char(20020101, '99999999'),'yyyymmdd');
> to_date
>------------
> 2003-08-12 WHY DOES IT GIVE ME DIFFERENT ANSWER WHEN I COMBINE THE
> FUNCTIONS?
>(1 row)
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Chris Pesko <cpesko(at)organic(dot)com> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: help on to_date conversion please.... |
Date: | 2002-04-03 23:47:48 |
Message-ID: | 20020403154655.Y98769-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
On Wed, 3 Apr 2002, Chris Pesko wrote:
> Never mind. Just figured it out. Need to grab the substring from 2 for 8
> from the to_char result because it holds character number 1 reserv ed for +
> or - signs. + is default or white space. I think that is the reason. Any
> other input?????
You might want to try the FM format on the to_char string...
sszabo=# select to_date(to_char(20020101, 'FM99999999'),'yyyymmdd');
to_date
------------
2002-01-01
(1 row)
From: | Chris Pesko <cpesko(at)organic(dot)com> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: help on to_date conversion please.... |
Date: | 2002-04-04 00:02:11 |
Message-ID: | 4.3.2.7.2.20020403160156.046526a0@mailhost.organic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
Thanks. That seems a better way to go.
At 03:47 PM 4/3/02 -0800, Stephan Szabo wrote:
>On Wed, 3 Apr 2002, Chris Pesko wrote:
>
> > Never mind. Just figured it out. Need to grab the substring from 2 for 8
> > from the to_char result because it holds character number 1 reserv ed for +
> > or - signs. + is default or white space. I think that is the reason. Any
> > other input?????
>
>You might want to try the FM format on the to_char string...
>sszabo=# select to_date(to_char(20020101, 'FM99999999'),'yyyymmdd');
> to_date
>------------
> 2002-01-01
>(1 row)
From: | Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> |
---|---|
To: | Chris Pesko <cpesko(at)organic(dot)com> |
Cc: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>, pgsql-admin(at)postgresql(dot)org |
Subject: | Re: help on to_date conversion please.... |
Date: | 2002-04-04 08:36:43 |
Message-ID: | 20020404103642.D20127@zf.jcu.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-admin |
On Wed, Apr 03, 2002 at 04:02:11PM -0800, Chris Pesko wrote:
> Thanks. That seems a better way to go.
The PostgreSQL docs is your good friend :-)
> At 03:47 PM 4/3/02 -0800, Stephan Szabo wrote:
>
> >You might want to try the FM format on the to_char string...
> >sszabo=# select to_date(to_char(20020101, 'FM99999999'),'yyyymmdd');
> > to_date
> >------------
> > 2002-01-01
> >(1 row)
The extra space is very common for all to_...() outputs/inputs and is
needful thing of this. Don't ask me why.. it's Oracle idea.
Karel
--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz