暫存表判斷 / 產生日期暫存表

參考網址：http://www.blueshop.com.tw/board/show.asp?subcde=BRD20050616212814W4M&fumcde=FUM20041006152735ZFS

---

-- #暫存表判斷並刪除
IF OBJECT_ID('tempdb..#tmpDate') IS NOT NULL
DROP TABLE #tmpDate
go

DECLARE @sd datetime --開始日期
DECLARE @ed datetime --結束日期
DECLARE @d1 datetime --處理日期
DECLARE @d2 varchar --處理民國日期

set @sd='2008-09-11'
set @ed='2009-01-14'
set @d1=@sd

-- 先轉一筆資料入 #tmpDate 暫存表 drop table #tmpDate
select D_date '西元日期', weeknum '星期別', convert(varchar(4),DATEPART ("year", @d1)-1911)+right(replace(D_date,'-','/'),6) '民國日期' into #tmpDate
from(select convert(varchar(10),@d1,121) D_date, DATEPART ( "weekday" , @d1 )-1 weeknum ) a
select @d1=DATEADD("day" , 1, @d1 ) --加一天

WHILE (@d1 <= @ed) BEGIN insert into #tmpDate select *, convert(varchar(4),DATEPART ("year", @d1)-1911)+right(replace(D_date,'-','/'),6) C_date from(select convert(varchar(10),@d1,121) D_date, DATEPART ( "weekday" , @d1 )-1 weeknum ) a select @d1=DATEADD("day" , 1, @d1 ) END -- 顯示暫存表
select * from #tmpDate
order by [西元日期]