西元年 -> 民國年

Select (to_number(to_char(sysdate,'yyyy')) - 1911)to_char(sysdate,'/MM/DD HH24:MI:SS') AS AAD from dual;

select to_char(add_months(sysdate,-(1911*12)),'YY/MM/DD HH24:MI:SS') from dual;

select add_months(sysdate,-(1911*12)) from dual

-t
---
Tyson Chen
tysonchen () gmail

取重複值

select * from global_deptgroup by dept_no,dept_namehaving count(dept_name)>1

取重複值

-t
---
Tyson Chen
tysonchen () gmail

Can one drop a column from a table?

Can one drop a column from a table?From Oracle8i one can DROP a column from a table. Look at this sample script, demonstrating the ALTER TABLE table_name DROP COLUMN column_name; command.
Other workarounds: 1. SQL> update t1 set column_to_drop = NULL;
SQL> rename t1 to t1_base;
SQL> create view t1 as select from t1_base;

2. SQL> create table t2 as select from t1;
SQL> drop table t1;
SQL> rename t2 to t1;

===========
rem -----------------------------------------------------------------------
rem Filename: dropcol.sql
rem Purpose: Demonstrate Oracle8i drop column functionality
rem Date: 28-Aug-1998
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

-- drop table x
-- /

create table x(a date, b date, c date)
/

-- Drop column B:
alter table x set unused column b -- Mark col as UNUSED
/
select * from sys.dba_unused_col_tabs
/
alter table x drop unused columns
/

-- Drop column C (different method):
alter table x drop column c cascade constraints
/

=================
-t
---
Tyson Chen
tysonchen () gmail

給我整個月,其餘免談

select create_date from break_report
where create_date between to_date('2004/07/01 00:00:00','YYYY/MM/DD HH24:MI:SS')
and last_day(to_date('2004/07/01 23:59:59','YYYY/MM/DD HH24:MI:SS'))

-t
---
Tyson Chen
tysonchen () gmail

當預約日期小於今天就不要show

select * from tt where to_date('2004' || '/' || tmonth || '/' || tday,'yyyy/mm/dd') > mdate

-t
---
Tyson Chen
tysonchen () gmail

DATE SQL

select * from test where TO_CHAR(tdate,'MM') = 7

---
Tyson Chen
tysonchen () gmail

oracle 8 manual

http://www-rohan.sdsu.edu/doc/oracle/server803/A54647_01/index.htm


---
Tyson Chen
tysonchen () gmail