Thread: help on to_date conversion please....

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)

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@postgresql.org

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)

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)

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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz