HSQLDB creating table for unit test for sqlserver

Question

I am creating table for my sqlserver unit test using hsqldb. Here is my syntax:

SET DATABASE SQL SYNTAX MSS TRUE;

DROP TABLE MY_STUDIES IF EXISTS;
CREATE TABLE MY_STUDIES
(
STUDY_ID INT,
IB_ID INT NOT NULL,
STUDY_DATE DATE,
CREATION_DATE DATE,
STUDY_UID VARCHAR(200),
PRIMARY KEY (STUDY_ID)
);

Everything works well besides STUDY_DATE and CREATION_DATE. Is DATE a good format here? Is there other DATE type exists for sqlserver here?

Have you have a look at the documentation? [Data types (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15) — Thom A, Sep 03 '20 at 12:25
DATE, TIME, DATETIME2 and DATETIMEOFFSET are actual datatypes. DATETIME and SMALLDATETIME are not recommanded because of edge effect and lack of precision. — SQLpro, Sep 03 '20 at 12:30
https://phauer.com/2017/dont-use-in-memory-databases-tests-h2/ — , Sep 03 '20 at 12:35

score 0 · Answer 1 · answered Sep 04 '20 at 06:25

SQL Server documentation says the following:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

The SQL Standard types are TIME, DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE. Microsoft uses DATETIME2 and DATETIMEOFFSET as aliases for TIMESTAMP (WITH TIME ZONE).

Your use of DATE in the table definition seems to be fine. DATE is represented as a YYYY-MM-DD string according to the SQL Standard.

HSQLDB creating table for unit test for sqlserver

1 Answers1