Search code examples
sqldatetimedefaultsqldatatypes

Creating SQL table where empty string on DATETIME or INT doesn't default

How do I create a table so that an INSERT INTO with empty string doesn't default to Jan 1, 1900 for DATETIME data type, or 0 for INT data type?

CREATE TABLE TEST
(
  TS DATETIME,
  VA INT
)

INSERT INTO TEST VALUES ('','')
INSERT INTO TEST VALUES ('','2')
INSERT INTO TEST VALUES ('2012-06-08 12:13:14','')

SELECT * FROM TEST

SQL FIDDLE

Solution

  • Don't try to insert empty strings '' for the DATETIME column.

    Use NULL instead.

    This will work if you define the column as NULL.

    INSERT INTO TEST VALUES (NULL,'')
INSERT INTO TEST VALUES (NULL,'2')
INSERT INTO TEST VALUES ('2012-06-08 12:13:14','')