Problems with TO_CHAR function (invalid format and non evaluation in SELECT statement)

[Vincent Privat]

Hi,

I'm new to H2 and need to run some SQL requests originally written for Oracle.

One request involves the TO_CHAR and TO_DATE Oracle functions.

As TO_DATE is not yet supported I have added an alias for it. Not yet tested but at least H2 knows about my alias.

However, I think I'm facing two problems with TO_CHAR (using latest H2 version, 1.4.182, 2014-10-17).

Considering this table:

CREATE TABLE CCH ( 

id NUMBER ( 8 ) NOT NULL, 

since TIMESTAMP NOT NULL, 

until TIMESTAMP, 

CONSTRAINT PK119 PRIMARY KEY (id)

);

An example of request is:

SELECT

id,

TO_CHAR(since,'YYYY/MM/DD HH24:MI:SS'),

TO_CHAR(until,'YYYY/MM/DD HH24:MI:SS')

FROM CCH

WHERE (since >= TO_DATE(?, 'YYYY/MM/DD HH24:MI:SS') ) ORDER BY id;

Problem 1:

---------------

When running this request, I get the following exception:

org.h2.jdbc.JdbcSQLException: Column "TO_CHAR(SINCE,YYYY/MM/DDHH24:MI:SS)" not found [42122-176]

which makes me think H2 does not evaluate the TO_CHAR function in SELECT statements. Am I right? Should I create a new defect ticket for this? Sadly I have no idea how to provide a patch to fix this, if it's confirmed it's a bug.

Problem 2:

---------------

To be sure H2 supports the format written in my request I made a small Java Program:

import java.math.BigDecimal;

import org.h2.util.ToChar;

public class Test {

public static void main(String[] args) {

System.out.println(ToChar.toChar(new BigDecimal(0), "YYYY/MM/DDHH24:MI:SS", null));

}

}

Unfortunately it seems this format is not yet supported as I get:

org.h2.jdbc.JdbcSQLException: Invalid TO_CHAR format "YYYY/MM/DDHH24:MI:SS" [90010-182]

Same question, should I create a new defect ticket for this? I think I could maybe write a patch for this point.

[Vincent Privat]

After further investigation I found the issues were not coming from H2 but from my software (problem 1 was a wrong spelling of column name when fetching results; problem 2 was calling wrong method accepting a bigdecimal instead of a timestamp).

The TO_CHAR function now works like a charm, thanks for having implemented it!

[Sam Blume]

I've contributed the TO_DATE (and TO_TIMESTAMP) code to H2 and it should be in Version 1.4.191 (It's not mentioned in the changelog - yet). 

Note: If you have programed an alias with that name, you need to delete that alias first or H2 will complain of having a name clash.