[edit]
1 Type1 #
WITH Dim_Date
AS
(
SELECT 1 Seq, CONVERT(datetime, '20030101') DT
UNION ALL
SELECT Seq + 1, DATEADD(hh, 1, DT) FROM Dim_Date
WHERE DATEADD(hh, 1, DT) <= GETDATE()
)
SELECT
Seq DateKey
, CONVERT(int, CONVERT(char(8), DT, 112)) DT_YYYYMMDD_int
, DATEPART(hh, DT) DT_HH_tinyint
, CONVERT(char(8), DT, 112) DT_YYYYMMDD_char
, CONVERT(char(2), DT, 108) DT_HH_char
, DT DT_DateTime
, DATEPART(yyyy, DT) YYYY
, CASE WHEN DATEPART(mm,DT) < 7 THEN 'H1' ELSE 'H2' END HALF
, DATEPART(qq, DT) QQ
, DATEPART(mm, DT) MM
, DATEPART(dd, DT) DD
, DATEPART(dy, DT) DY
, DATEPART(week, DT) WEEK
, DATEPART(weekday, DT) WEEKDAY
, CASE
WHEN DATEPART(weekday, DT) = 1 THEN ''
WHEN DATEPART(weekday, DT) = 2 THEN ''
WHEN DATEPART(weekday, DT) = 3 THEN ''
WHEN DATEPART(weekday, DT) = 4 THEN ''
WHEN DATEPART(weekday, DT) = 5 THEN '覈'
WHEN DATEPART(weekday, DT) = 6 THEN '蠍'
WHEN DATEPART(weekday, DT) = 7 THEN ''
END WEEKDAY_KOR
FROM Dim_Date
OPTION (MAXRECURSION 0);
[edit]
2 Type2 #
WITH Dim_Date
AS
(
SELECT 1 Seq, CONVERT(datetime, '20080701') DT
UNION ALL
SELECT Seq + 1, DATEADD(dd, 1, DT) FROM Dim_Date
WHERE DATEADD(dd, 1, DT) <= GETDATE()
)
SELECT
CONVERT(int, CONVERT(char(8), DT, 112)) DateKey
, CONVERT(char(8), DT, 112) DT_char8
, CONVERT(char(10), DT, 121) DT_char10
, DT DT_DateTime
, DATEPART(yyyy, DT) YYYY
, CASE WHEN DATEPART(mm,DT) < 7 THEN 'H1' ELSE 'H2' END HALF
, DATEPART(qq, DT) QQ
, DATEPART(mm, DT) MM
, DATEPART(dd, DT) DD
, DATEPART(dy, DT) DY
, DATEPART(week, DT) WEEK
, DATEPART(weekday, DT) WEEKDAY
, CASE
WHEN DATEPART(weekday, DT) = 1 THEN ''
WHEN DATEPART(weekday, DT) = 2 THEN ''
WHEN DATEPART(weekday, DT) = 3 THEN ''
WHEN DATEPART(weekday, DT) = 4 THEN ''
WHEN DATEPART(weekday, DT) = 5 THEN '覈'
WHEN DATEPART(weekday, DT) = 6 THEN '蠍'
WHEN DATEPART(weekday, DT) = 7 THEN ''
END WEEKDAY_KOR
, CONVERT(int, CONVERT(char(8), DATEADD(dd, -6, DT), 112)) Begin_Week1
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 0, DT), 112)) End_Week1
, CONVERT(int, CONVERT(char(8), DATEADD(dd, -5, DT), 112)) Begin_Week2
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 1, DT), 112)) End_Week2
, CONVERT(int, CONVERT(char(8), DATEADD(dd, -4, DT), 112)) Begin_Week3
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 2, DT), 112)) End_Week3
, CONVERT(int, CONVERT(char(8), DATEADD(dd, -3, DT), 112)) Begin_Week4
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 3, DT), 112)) End_Week4
, CONVERT(int, CONVERT(char(8), DATEADD(dd, -2, DT), 112)) Begin_Week5
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 4, DT), 112)) End_Week5
, CONVERT(int, CONVERT(char(8), DATEADD(dd, -1, DT), 112)) Begin_Week6
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 5, DT), 112)) End_Week6
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 0, DT), 112)) Begin_Week7
, CONVERT(int, CONVERT(char(8), DATEADD(dd, 6, DT), 112)) End_Week7
FROM Dim_Date
OPTION (MAXRECURSION 0);
.. 譯朱 豎狩..
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-01'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week1 = b.begin_dt
, end_week1 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-02'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week2 = b.begin_dt
, end_week2 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-03'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week3 = b.begin_dt
, end_week3 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-04'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week4 = b.begin_dt
, end_week4 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-05'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week5 = b.begin_dt
, end_week5 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-06'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week6 = b.begin_dt
, end_week6 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
;with ww
as
(
select
date_key
, row_number() over(order by date_key) rownum
from (
select
date_key
--, row_number() over(order by date_key) rownum
, row_number() over(order by date_key) % 7 rownum
from [dim].[date]
where date_key >= '2000-01-07'
) t
where rownum in (1, 0)
), rs
as
(
select
begin_dt
, end_dt
from (
select
a.date_key begin_dt
, b.date_key end_dt
, row_number() over(order by a.date_key) rownum
from ww a
inner join ww b
on a.rownum = b.rownum - 1
) t
where rownum % 2 = 1
)
update a
set
begin_week7 = b.begin_dt
, end_week7 = b.end_dt
from dim.date a
inner join rs b
on a.date_key between b.begin_dt and b.end_dt
鏤