CODEWITHSUNDEEP
oracle-databasetimestampdefaultalter-table
Krrish
Krrish

Reputation: 1

Alter a timestamp column in an oracle table with default value as '0000-00-00 00:00:00'

Can we assign a default value '0000-00-00 00:00:00' in oracle as we do in mysql as shown below ?

Sample query in mysql:

ALTER TABLE . MODIFY COLUMN TIMESTAMP DEFAULT '0000-00-00 00:00:00';

Upvotes: 0

Views: 1912

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

No - in Oracle neither year, month, or day can be set to zero. I suggest using NULL instead.

dbfiddle here

EDIT

Of course, having now gone back through one of my old questions and crawled down a wormhole or two I see that you can get a year of zero to be accepted - but it still appears that month and day cannot be zero. To get the year of zero in you have to use an ANSI date literal - e.g. DATE '0000-01-01' is considered acceptable. I don't know if the various date routines and date calculations will like this - for example, TO_CHAR(DATE '0000-01-01', 'DD-MON-YYYY') produces a result of '00-000-0000', which is certainly not what I'd expect, but perhaps it's good enough for your purposes. Note that you can't go the other way with this - TO_DATE('00-000-0000', 'DD-MON-YYYY') produces the expected ORA-01847 - day of month must be between 1 and last day of month error.

Does anybody really know what time it is..? :-)

Upvotes: 1

Related Questions