Обсуждение: Problem with to_date function

Hi,

I would like to know if the following is the expected result:
from psql:
bcoproducao=# select to_date(current_date, 'DD/MM/YYYY'), current_date;
  to_date   |    date
------------+------------
 22/08/2005 | 22/08/2005
(1 row)

from my jdbc code:
            rs = stmt.executeQuery("select to_date(current_date,
'DD/MM/YYYY'), current_date");
            while (rs.next()) {
                System.out.println(rs.getString(1) + " | "
+rs.getString(2));
            }
result:
            0028-01-26 | 2005-08-22

Any advice here?
Thanks!
Gilberto

On Monday 22 August 2005 15:31, Gilberto C Andrade wrote:
> Hi,
>
> I would like to know if the following is the expected result:
> from psql:
> bcoproducao=# select to_date(current_date, 'DD/MM/YYYY'), current_date;
>   to_date   |    date
> ------------+------------
>  22/08/2005 | 22/08/2005
> (1 row)
>
> from my jdbc code:
>             rs = stmt.executeQuery("select to_date(current_date,
> 'DD/MM/YYYY'), current_date");
>             while (rs.next()) {
>                 System.out.println(rs.getString(1) + " | "
> +rs.getString(2));
>             }
> result:
>             0028-01-26 | 2005-08-22

Hate to burst your bubble:

psql=# select to_date(current_date, 'DD/MM/YYYY'), current_date;
  to_date   |    date
------------+------------
 0028-01-26 | 2005-08-22
(1 row)

So there is something fishy with your use of to_date, most likely because
current_date is already of type DATE, and TO_DATE expects a string as it's
first parameter.

Lemme see:

psql=# select cast( current_date as text );
    text
------------
 2005-08-22
(1 row)

psql=# select to_date( '2005-08-22', 'DD/MM/YYYY');
  to_date
------------
 0028-01-26
(1 row)

So there you have it...

I think that basically you don't need that to_date at all, since you already
have a date...

JdV!!

>
> Any advice here?
> Thanks!
> Gilberto
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

On Mon, 22 Aug 2005, Gilberto C Andrade wrote:

> I would like to know if the following is the expected result:
> from psql:
> bcoproducao=# select to_date(current_date, 'DD/MM/YYYY'), current_date;
> to_date   |    date
> ------------+------------
> 22/08/2005 | 22/08/2005
> (1 row)
>

This query depends on the setting of the DateStyle parameter which
determines how current_date is turned into text before being passed into
to_date.  The JDBC driver sets DateStyle to ISO on connection startup so
that it can send and receive dates in this format.  Unfortunately it seems
to_date doesn't error out with "improper date format" or similar, but just
tries to make a best guess at what you really wanted.

Kris Jurka

On Mon, 22 Aug 2005, Gilberto C Andrade wrote:

> ALTER DATABASE bcoproducao SET DateStyle=sql, european;
> ALTER DATABASE bcoproducao SET client_encoding=latin1;
>
> So, setting datestyle isn't the problem.

Yes, it actually is.  Neither of the above settings will have any effect
because when the JDBC driver first connects to your database it will
overwrite these settings with ISO and UNICODE respectively.  You may
verify this by running "SHOW DateStyle" from a JDBC connection.  The
driver needs these settings to operate correctly.

> Other thing, I'm reporting this because we (www.secad.to.gov.br) have several
> reports (jasperreports) using to_date() function and all are returning wrong
> results.
>

I am unfamiliar with jasperreports, but I would suggest using
PreparedStatement.setDate which will correctly format your dates, or use
the ISO format for dates and the to_date format string.

Kris Jurka