SET LANGUAGE Turkish
;WITH DaySeries AS
(
SELECT PK_Date = CAST('2008-01-01' as date)
UNION ALL
SELECT DATEADD(DAY,1,PK_Date)
FROM dayseries
WHERE DATEADD(DAY,1,PK_Date)<'2023-01-01'
)
SELECT
Date_ID=ROW_NUMBER() OVER(ORDER BY PK_Date)
,*
,Day_Name= DATENAME(DAY,PK_Date)
,Month_Name= DATENAME(MONTH,PK_Date)
,Date_Name =DATENAME(DAY,PK_Date)+' '+DATENAME(MONTH,PK_Date)+' '+DATENAME(DW,PK_Date)
,Quarter_Name =CAST(Period_ID as nvarchar(2)) + '.Periyot'
,Half_Name =CAST(Half_ID as nvarchar(2)) + '.Yarı'
,Week_Of_Year_Name=CAST(Week as nvarchar(2)) +'.Hafta'
FROM (
SELECT --top 730
PK_Date
,Day_ID=DATENAME(DAY,PK_Date)
,Month_ID=DATEPART(month,PK_Date)
,Year_ID =DATENAME(YEAR,PK_Date)
,Period_ID=CEILING((CAST(DATEPART(month,PK_Date) as numeric(6,4))/3))
,Half_ID=CEILING((CAST(DATEPART(month,PK_Date) as numeric(6,4))/6))
,Week= DATEPART(WEEK,PK_Date)
,WeekDay_ID= DATEPART(WEEKDAY,PK_Date)
,WorkDay= CASE DATEPART(WEEKDAY,PK_Date) WHEN 6 THEN 0 WHEN 7 THEN 0 ELSE 1 END
FROM dayseries
) as sub OPTION (MAXRECURSION 10000)