Skip to Main Content
  • Questions

  • Table column with default date value

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ganesh.

Asked: October 06, 2016 - 12:07 pm UTC

Last updated: February 13, 2018 - 1:29 am UTC

Version: 10g

Viewed 50K+ times! This question is

You Asked

Hi Tom,

How to create a table with one column with date datatype having default date value as '01-01-2001'?

The below script is throwing error and i am unable to create a table,please help me on this issue.

create table test_date (date_check date default ('01-01-2001'));

and Chris said...

Ug, implicit conversions!

Oracle is trying to convert the string '01-01-2001' to a date. But it can't.

To avoid this, either pass it as an ANSI literal date:

create table test_date (date_check date default (date'2001-01-01'));


Or use to_date with an appropriate format mask:

create table test_date (date_check date default (to_date('01-01-2001', 'dd-mm-yyyy')));

Always convert date strings into real dates when storing them!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 12, 2018 - 12:10 pm UTC

Hi Tom,

How can we know date format of any table?
Connor McDonald
February 13, 2018 - 1:29 am UTC

Every date column in every table inside Oracle has the same format. It is 7 bytes (century, year, month, day, hour, min, sec).

The way you *see* a date is totally under the control of your session. The database provides a default, but the it ultimately the client that makes the decision.

SQL> select sysdate from dual;

SYSDATE
---------
13-FEB-18

1 row selected.

SQL> alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
13/02/2018 09:28:53

1 row selected.

SQL> alter session set nls_date_format = 'yyyy/mm/dd';

Session altered.

SQL> select sysdate from dual;

SYSDATE
----------
2018/02/13

1 row selected.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.