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

[Bernd Ruehlicke]

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

[Bernt M. Johnsen]

(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 M. Johnsen]

Post by Bernd Ruehlicke
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)