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