help on to_date conversion please.... 
Author Message
 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)

---------------------------(end of broadcast)---------------------------



Mon, 20 Sep 2004 07:26:45 GMT
 help on to_date conversion please....

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)


Quote:
>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)---------------------------


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Mon, 20 Sep 2004 07:40:42 GMT
 help on to_date conversion please....
Thanks.  That seems a better way to go.


Quote:

> > 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)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Mon, 20 Sep 2004 08:00:21 GMT
 help on to_date conversion please....

Quote:

> 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)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Mon, 20 Sep 2004 08:08:31 GMT
 help on to_date conversion please....

Quote:

> Thanks.  That seems a better way to go.

 The PostgreSQL docs is your good friend :-)

Quote:

> >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

--

 http://home.zf.jcu.cz/~zakkr/

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

---------------------------(end of broadcast)---------------------------



Mon, 20 Sep 2004 18:48:50 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. character-2-ascii conversion please please please help !

2. to_date help please

3. COMBO BOX, PLEASE HELP, PLEASE HELP, PLEASE HELP!

4. Date Conversion Please Help

5. DATE CONVERSION HELP PLEASE

6. Reflex database conversion, please help!

7. please help with date conversion error 4 collection.

8. Datetime Conversion Error PLEASE HELP URGENT!!

9. BCP woes with unimplemented conversions - please help!

10. Please HELP:Hexadecimal conversion error????

11. Please help on uniVerse printing & data conversion

12. Stored Proc Date conversion Problem, Please Help


 
Powered by phpBB® Forum Software