db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Has any made a "to_char" function - similar as the Oracle verison ?
Date Thu, 21 Apr 2005 09:07:15 GMT
>>>>>>>>>>>> Bernd Ruehlicke wrote (2005-04-20 15:53:10):
> Hi all,
> I see that we have the "CHAR" method - but I was more looking for a
> function like the Oracle "to_char".
> The power here is that the second argument can have a time-date format
> string allowing to define how the output text string should be
> generated. I.e. something alla
> SELECT to_char(start_date,'YYYY-MM-DD') FROM bla.... 
> which will return the date in the 'YYYY-MM-DD' format.
> ... if anybody has made such a function it would be wonderful to have
> this added to the list of default function of Derby I would say. - Of
> course it would not hurt to also add the "to_number" function as well

I would say the following (allthough I admit that "to_char" may be
convenient in some cases):

1) Derby operates (both in SQL and in the JDBC driver) with ISO 8601
   dates which is conformant with the standards and is the format
   dates should have.

2) Conversion to or from some localized or non-stabdard format should
   be done on the application level (e.g with

3) If you really want to convert dates in SQL, it is possible to do
   it in a standard way, e.g.:

(given a table "some_table" with a DATE column "iso_date")

            WHEN m=2 THEN 'Feb'
            WHEN m=3 THEN 'Mar'
            WHEN m=4 THEN 'Apr'
            WHEN m=5 THEN 'May'
            WHEN m=6 THEN 'Jun'
            WHEN m=7 THEN 'Jul'
            WHEN m=8 THEN 'Aug'
            WHEN m=9 THEN 'Sep'
            WHEN m=10 THEN 'Oct'
            WHEN m=11 THEN 'Nov'
            WHEN m=12 THEN 'Dec' 
        END || ' ' ||
        d || ', ' ||
        y AS us_date
     FROM (SELECT {fn SUBSTR(CAST(iso_date AS CHAR(10)),1,4)} AS y ,
                  CAST({fn SUBSTR(CAST(iso_date AS CHAR(10)),6,2)} AS INTEGER) AS m, 
                  {fn SUBSTR(CAST(iso_date AS CHAR(10)), 9,2)} AS d
           FROM some_table) AS d3;

(I use {fn....} since SUSBTR(...) is not standard SQL but defined in JDBC)

Bernt Marius Johnsen, Database Technology Group, 
Sun Microsystems, Trondheim, Norway

View raw message