Insert a DATE into H2 database

Question

I want to insert a date into my H2 database using a sql script. Now I have the following:

CREATE TABLE CUSTOMERS (
  ID int NOT NULL,
  FIRSTNAME varchar(50) NOT NULL,
  LASTNAME varchar(50) NOT NULL,
  GENDER varchar(50) NOT NULL,
  COMPANYID varchar(50) NOT NULL,
  EMAIL varchar(50) NOT NULL,
  BIRTHDAY date NOT NULL,
  CREATEDAT date NOT NULL,
  UPDATEDAT date NOT NULL,
  PRIMARY KEY (ID)
);
INSERT INTO CUSTOMERS (
  ID,
  FIRSTNAME,
  LASTNAME,
  GENDER,
  COMPANYID,
  EMAIL,
  BIRTHDAY,
  CREATEDAT,
  UPDATEDAT
)
VALUES (
  1, 
  'Lee', 
  'Diaz', 
  'male', 
  '159', 
  '[email protected]', 
  '05-08-1912', 
  '13-12-2019', 
  '30-09-2021'
)

For some reason the dates don't work, but I don't get why. Does anyone know?

Answer 1

There are standard SQL literals for date-time values:

• DATE '2022-12-31' for DATE data type.
• TIME '23:59.59, TIME '23.59.59.123456', etc., for TIME data type.
• TIME WITH TIME ZONE '23:59:59+07:30' for TIME WITH TIME ZONE data type.
• TIMESTAMP '2022-12-31 23.59.59' for TIMESTAMP.
• TIMESTAMP WITH TIME ZONE '2022-12-31 23.59.59+07:30' for TIMESTAMP WITH TIME ZONE.

Various database systems may support own extensions. In H2 you can specify a string '2022-12-31' in your insert statement instead of date literal and many people do that. In datetime arithmetic expressions, however, such replacement may not work, because various expressions work in different way with different data types, '2022-12-31' is a character string, it isn't a date. But its explicit or implicit conversion to DATE produces a date value.

H2 supports ISO 8601-style datetime literals with T instead of space between date and time. (Datetime and interval values in SQL are different from ISO 8601 in general.) H2 supports time zone names in timestamp with time zone literals, but H2 always converts these time zones to simple time zone offsets.

H2 supports years with negative numbers and years with more than 4 digits in number.

In some compatibility modes you can use additional alternative syntaxes, but usually you should avoid them.

There are also various datetime parsing and formatting functions, but I think you don't need them here.