關于Oracle日期轉換的一點小經驗：運用union的一種奇怪現象及解決
2024-08-29 13:47:47
供稿：網友
Oracle9i安裝默認的日期格式是‘DD-MM-RR’，這個可以通過
select * from sys.nls_database_parameters;
進行查看。
因此對于輸入參數為DATE類型的存儲過程就出現問題了，其中查詢基本表（tranficstat
）里記錄日期格式為‘yyyy-mm-dd’。原碼如下：
--比較某兩個車站相同時間段的運力情況
create or replace PRocedure HY_CONTRAST_PERIOD(
depotcode1 in varchar2,
depotcode2 in varchar2,
startdate1 in date,
enddate1 in date,
cur_return out CUR_DEFINE.GB_CUR) is --CUR_DEFINE.GB_CUR 是自定義的游標類型
begin
case
when (depotcode1 is null) or (depotcode2) is null then
return;
else
open cur_return for
select
sum(NORMAL_SCHEMES) as 正班班次,
sum(OVERTIME_SCHEMES) as 加班班次,
sum(NORMAL_SEATS) as 正班座位數,
sum(OVERTIME_SEATS) as 加班座位數
from tranficstat
where senddate >= startdate1
and senddate < enddate1+ 1
and depot = depotcode1
group by depot
union
select
sum(NORMAL_SCHEMES) as 正班班次,
sum(OVERTIME_SCHEMES) as 加班班次,
sum(NORMAL_SEATS) as 正班座位數,
sum(OVERTIME_SEATS) as 加班座位數
from tranficstat
where senddate >= startdate1
and senddate < enddate1 + 1
and depot = depotcode2
group by depot;
end case;
end HY_CONTRAST_PERIOD;
通過union,你期望返回兩條記錄，卻發現永遠總是只返回一條記錄。問題癥結發生在日期格式轉換上，參數傳進的格式為‘dd-mm-rr’，而條件左側的記載格式為‘yyyy-mm-dd’，只要把所有右側條件更改成如
where senddate >= to_date(to_char(startdate1,'yyyy-mm-dd'),'yyyy-mm-dd')
and senddate < to_date(to_char(enddate1,'yyyy-mm-dd'),'yyyy-mm-dd') + 1;
即可消除癥狀。
當然也可以修改左側的格式，總之使兩邊的日期格式匹配；另外當然也可以直接修改系統的NLS_DATE_FORMAT 。
