将日期插入H2数据库
我想使用SQL脚本将日期插入我的H2数据库。现在我有以下内容：
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'
)
由于某种原因，日期不起作用，但我不明白为什么。有人知道吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
日期时间值有标准的SQL文字：
日期'2022-12-31'date数据类型。时间'23：59.59'，时间'23 .59.59.59.59.123456'等，用于time数据类型。时间区域'23：59：59+07：30'用于时间区域区域数据类型的时间。TIMESTAMP'2022-12-31 23.59.59'用于TIMESTAMP。时间戳记与时区'2022-12-31 23.59.59+07：30'fortimestamp带时区域区域。各种数据库系统可以支持自己的扩展。在H2中，您可以在插入语句中而不是文字中指定字符串
'2022-12-31'，许多人这样做。但是，在DateTime算术表达式中，此类替换可能不起作用，因为各种表达式与不同的数据类型不同，'2022-12-31'是字符串，不是日期。但是它的明确或隐式转换为日期产生日期值。H2支持具有
t的ISO 8601风格的DateTime文字，而不是日期和时间之间的空间。 （SQL中的DateTime和间隔值一般不同于ISO 8601。）H2支持时间戳中的时区名称和时区文字，但是H2始终将这些时区转换为简单的时区偏移。H2支持年数和年数年数，数量超过4位数字。
在某些兼容模式中，您可以使用其他替代语法，但通常您应该避免使用它们。
还有各种DateTime解析和格式化功能，但我认为您在这里不需要它们。
There are standard SQL literals for date-time values:
DATE '2022-12-31'forDATEdata type.TIME '23:59.59',TIME '23.59.59.123456', etc., forTIMEdata type.TIME WITH TIME ZONE '23:59:59+07:30'forTIME WITH TIME ZONEdata type.TIMESTAMP '2022-12-31 23.59.59'forTIMESTAMP.TIMESTAMP WITH TIME ZONE '2022-12-31 23.59.59+07:30'forTIMESTAMP 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 toDATEproduces a date value.H2 supports ISO 8601-style datetime literals with
Tinstead 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.
我们可以尝试使用 iso_8601 dateTime插入值的格式。
We can try to use ISO_8601 format for DateTime inserted value.