主流数据库 DATE 类型适用指南

最新推荐文章于 2025-02-05 14:16:11 发布

原创最新推荐文章于 2025-02-05 14:16:11 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#date #数据库 #sqlserver #string #informix #insert

本文对比了六种不同数据库系统（Oracle、DB2、Informix、SQL Server、MySQL 和 Sybase）中日期时间类型的创建及插入操作。每种数据库都有其特定的日期时间格式和语法，了解这些差异有助于跨平台开发。

1， ORACLE

@public final static String ORACLE = "create TABLE date_type_test (date_t date, timestamp_t timestamp(3), interval_t interval YEAR TO MONTH)";

@public final static String ORACLE_INS = "insert into date_type_test values(SYSDATE,TO_TIMESTAMP('2004-4-9 22:59:43.234232222', 'YYYY-MM-DD HH24:MI:SS.FF'), INTERVAl '1-2' YEAR TO MONTH)";

2, DB2

@public final static String DB2 = "create TABLE datatypetest (date_t date, time_t time, timestamp_t timestamp)";
@public final static String DB2_INS = "insert into datatypetest values('01/07/1999', '12:12:23','2002-11-14-12.12.23.00') ";

3, INFORMIX

public final static String INFORMIX = "create TABLE date_type_test (date_t date, datetime_t datetime year to fraction(5), interval_t interval year to month)";
public final static String INFORMIX_INS = "insert into date_type_test values('1999-07-01', '1999-07-01 12:00:00.00001','1-1')";

4,SQLSERVER

###timestamp is not a date type.

public final static String SQLSERVER = "create TABLE date_type_test (smalldatetime_t smalldatetime, datetime_t datetime)";
public final static String SQLSERVER_INS = "insert into date_type_test values ('12:00:01', '01/07/1999')";

5, MYSQL

public final static String MYSQL = "create TABLE date_type_test (date_t date, time_t time, year_t year, datetime_t datetime, timestamp_t timestamp)";
public final static String MYSQL_INS = "insert into date_type_test values( '1999-07-01','12:00:01', '2006','2006-03-24 12:00:00', '20060324120000')";

6, sybase

###timestamp is returned as a binary array

public final static String SYBASE = "create TABLE date_type_test (smalldatetime_t smalldatetime, datetime_t datetime, timestamp_t timestamp)";
public final static String SYBASE_INS = "insert into date_type_test values('1999-04-01 12:00:00','1999-04-01 12:00:00','1')";