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:26:28 GMT
>>>>>>>>>>>> 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

Mime
View raw message