SQL Note
將民國年含零欄位轉換為西元年
select p.AWK_DATE_E,
cast((
cast((cast((select SUBSTRING(p.AWK_DATE_E,1,3)) as int) + 1911) as nvarchar)+’/’+
cast((select REPLACE(SUBSTRING(p.AWK_DATE_E,4,2),’0′,”)) as nvarchar)+’/’+
cast((select REPLACE(SUBSTRING(p.AWK_DATE_E,6,2),’0′,”)) as nvarchar)
)as date) as ‘AC Date’
from permission p
========================================
檢視資料庫概觀：
select table_name, column_name,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘wk_ok_date’
========================================
表格創立 – 欄位條件：自動編號、外來鍵
create table UTRoleDetail
(ID int primary key IDENTITY(1,1),
RoleID int REFERENCES UTRole (ID) )
========================================
INNER JOIN 複數以上表格之語法
select ap.case_no,ap.from_no,p.PMT_DATE as 許可證核發日 ,
wc.start_wk_date as 開工日期,
ap.wk_ck_date as 備查日期,
wk.apply_date as 書面竣工日,
wk.apply_date_real as 回報竣工日
from (((wk_ok_date wk
inner join permission p on wk.pmt_no = p.PMT_NO)
inner join apply ap on ap.CASE_NO = p.CASE_NO)
inner join wk_case wc on wc.PMT_NO = p.PMT_NO)
where wk.apply_date>ap.wk_ck_date –報竣日期/備查日期
or p.PMT_DATE>wc.start_wk_date –許可證核發/開工