Has any made a "to_char" function - similar as the Oracle verison ?

[Bernd Ruehlicke <BR...@lgc.com>]

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

B-)

Re: Has any made a "to_char" function - similar as the Oracle verison ?

["Bernt M. Johnsen" <Be...@Sun.COM>]

>>>>>>>>>>>> Bernt M. Johnsen wrote (2005-04-21 11:07:15):
> (given a table "some_table" with a DATE column "iso_date")
> 
> SELECT CASE WHEN m=1 THEN 'Jan'
>             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)

Or more maybe more elegant:

SELECT
        mm.m || ' ' ||
        dd.d || ', ' ||
        dd.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 dd, 
           (VALUES (1, 'Jan'),
            (2, 'Feb'),
            (3, 'Mar'),
            (4, 'Apr'),
            (5, 'May'),
            (6, 'Jun'),
            (7, 'Jul'),
            (8, 'Aug'),
            (9, 'Sep'),
            (10, 'Oct'),
            (11, 'Nov'),
            (12, 'Dec')) as mm(i,m)
            WHERE mm.i = dd.m;

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

Re: Has any made a "to_char" function - similar as the Oracle verison ?

["Bernt M. Johnsen" <Be...@Sun.COM>]

>>>>>>>>>>>> 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
   java.text.SimpleDateFormat)

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

SELECT CASE WHEN m=1 THEN 'Jan'
            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