h2

Parsedatetime function does not work with 'yyyy' format

I am using 2.2.220 version of h2 database and it seems like parsedatetime function does not work properly. Below is my sql query and error response from h2.

Query:

select parsedatetime(formatdatetime("ds"."SALEDATE",'yyyy'),'yyyy') as "tg_year_over_ds_SALEDATE", max("ds"."PLANNEDSALES") as "mt_ds_PLANNEDSALES_max", min("ds"."PLANNEDSALES") as "mt_ds_PLANNEDSALES_min", sum("ds"."PLANNEDSALES") as "mt_ds_PLANNEDSALES_sum", count("ds"."PLANNEDSALES") as "mt_ds_PLANNEDSALES_count", avg("ds"."PLANNEDSALES") as "mt_ds_PLANNEDSALES_avg"
from "integration_tests"."connector_test" as "ds"
group by "tg_year_over_ds_SALEDATE"

Error: Caused by: org.h2.jdbc.JdbcSQLDataException: Error parsing "2024";

In H2Template TRUNC_YEAR function is defined as "parsedatetime(formatdatetime({0},'yyyy'),'yyyy')" but it is not working.

Solution

  • You cannot use non-standard Java-style PARSEDATETIME function with incomplete formats such as 'yyyy' in modern versions of H2, because they use java.time.format.DateTimeFormatter instead of legacy API from historic versions of Java. You can use standard cast specification with incomplete formats, but cast specification will fill missing fields with first day of the current month at midnight as required by the SQL Standard. Please note that the mentioned function and cast specification use different formats not compatible with each other. Cast specification can be used for both parsing and formatting:

    SELECT CAST(DATE '2024-08-29' AS VARCHAR FORMAT 'YYYY');
> '2024'

SELECT CAST('2024' AS DATE FORMAT 'YYYY');
> DATE '2024-08-01'

    If you need to truncate a date to the first day of the year, you can use another non-standard function DATE_TRUNC:

    SELECT DATE_TRUNC(YEAR, DATE '2024-07-30');
> DATE '2024-01-01'

    If you need only a year as a numeric value, you can use standard extract expression:

    SELECT EXTRACT(YEAR FROM DATE '2024-07-30');
> 2024