How to insert blank value in SQL database date column?

Question

I have one database in which there is one column named START_TS of type (datetime,not null). I wrote one program in java where I want to insert a blank value in that column but its taking
'1900-01-01 00:00:00.000' as the value.

statement.executeUpdate("insert into tableName(START_TS) values(CAST(START_TS as varchar(100)))");

Please suggest the casting mechanism where i can insert blank value in that column.

Thanks

Insert NULL if column constraints is NULL. insert into tableName(START_TS) values (NULL) — KV Prajapati
– KV Prajapati, Commented Sep 5, 2012 at 5:19
you cannot insert blank in the case of date you can use null. — Dinup Kandel
– Dinup Kandel, Commented Sep 5, 2012 at 5:19
It cannot be done, you can add null, if you remove not null contraint , otherwise I dont suppose there is any option — ManMohan Vyas
– ManMohan Vyas, Commented Sep 5, 2012 at 5:20
@Patton In this case the value '1900-01-01 00:00:00.000' is being inserted .. which is better than Unix epoch for date column. — user166390
– user166390, Commented Sep 5, 2012 at 5:22
@Ars, that's the default behavior that your SQL database engine has. You can't do anything against it but to remove the NOT NULL constraint, then you could insert a NULL value. There is no blank (white spaces or '') data for DATETIME fields, that concept can apply only for CHAR and VARCHAR (and derived) datatypes. — Luiggi Mendoza
– Luiggi Mendoza, Commented Sep 5, 2012 at 6:18

Dane Balia · Accepted Answer · 2012-09-05 05:29:04Z

If you using MYSQL which the syntax appears to point at; you could do the following:

ALLOW_INVALID_DATES

Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation). This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

This mode is implemented in MySQL 5.0.2. Before 5.0.2, this was the default MySQL date-handling mode. As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES.

However like everyone has said in the comments, it's not possible to stick a " " in a field whose data type is DATETIME.

Collectives™ on Stack Overflow

How to insert blank value in SQL database date column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related