write a query in h2 database to convert date format

Question

I am writing junit tests that uses h2 . Oracle uses a different format ('03-february-20'). H2 database throws error for that format ('2020-02-03').

I need to convert a string 03-february-20 into date 2020-02-03 in a query in h2 database. Please let me know how to do it?

Answer 1

Use PARSEDATETIME for example:

create table DATE_EXAMPLE (
    EXAMPLE DATE
);
insert into DATE_EXAMPLE values ('2020-02-03');
select * from DATE_EXAMPLE where EXAMPLE = PARSEDATETIME('03-february-20','dd-MMMM-yy');

Answer 2

It is unclear exactly what you are doing. Oracle's DATE data type has no "format". Or rather, it is an internal, binary format. If you want to select a column of DATE datatype, and convert that to a string representation (for consumption by humans or some other process that expects a string representation of a date, then you would use the to_char function:

select to_char(my_date,'dd-Mon-yyyy') from mytable;

and use any format mask you need to produce the results you want. The format mask I used was the 'dd-Mon-yyyy', which would present today's date as 27-Feb-2020.