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