oracle中isdate,ISDATE function oracle

William Robertson wrote:Well, if your boss has a gun to your head I guess you could write the constraint something like this:

SQL>createtabledemo(start_date_numnumber(8));

Tablecreated.

SQL>altertabledemoaddconstraintvalidate_date_chk

2check(date'0001-01-01'

Tablealtered.

SQL>insertintodemo(start_date_num)values(20169999);

insertintodemo(start_date_num)values(20169999)

*

ERRORatline1:

ORA-01843:notavalidmonth

SQL>insertintodemo(start_date_num)values(20160101);

1rowcreated.SQL> create table demo (start_date_num number(8));  Table created.  SQL> alter table demo add constraint validate_date_chk  2 check (date '0001-01-01' < to_date(to_char(start_date_num),'YYYYMMDD'));  Table altered.  SQL> insert into demo (start_date_num) values (20169999); insert into demo (start_date_num) values (20169999)  * ERROR at line 1: ORA-01843: not a valid month  SQL> insert into demo (start_date_num) values (20160101);  1 row created.

However, as everyone else has been saying, storing dates as numbers is a terrible idea. In fact it's not just a terrible idea, it's one of the classic terrible ideas that everyone knows about and groan when they see it. It may make life simpler for the Java developers - one very specific use case - but it makes life much harder for everyone else that needs to use the database.

I suggested and even pushed  them back.Sent a discussion link to my boss and java developers(Haven't heard back anything from them. Looks like they both are on board with this idea). If they realize its a bad idea one day, it's easy convert back to date( single straight update).

Thanks for your suggestions!