/****** Object:  Table [dbo].[INT_Dates]    Script Date: 10/02/2008 13:44:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
DROP TABLE [dbo].[INT_Dates]
GO
CREATE TABLE [dbo].[INT_Dates](
	[The_Date] [datetime] NOT NULL,
    [Date_Name] [varchar](80) NULL, -- Month DD, YYYY 
    [DDMONYYYY] [varchar](11) NULL, -- 'DD-MON-YYYY' 
    [MMDDYYYY] [varchar](10) NULL, -- 'MM/DD/YYYY' 
    [YYYYMMDD] [varchar](10) NULL, -- 'YYYY/MM/DD'
    [DOW] [int] NULL, -- Day of the Week
    [DOM] [int] NULL, -- Day of the Month
    [DOY] [int] NULL, -- Day of the Year
    [DY]  [varchar](3) NULL, -- Day abbreviation
    [Day] [varchar](15) NULL, -- Day spelled out
    [WOY] [int] NULL, -- Week of the Year
    [MM] [int] NULL, -- Month Number
    [MON] [varchar](3) NULL, -- Month abbreviation
    [Month] [varchar](15) NULL, -- Month spelled out
    [Q] [int] NULL, -- Quarter
    [YYYY] [int] NULL, -- 'YYYY'
    [Holiday] [CHAR](1) NULL, 
    [Business_Day] [CHAR](1) NULL, 
    [Remark] [varchar](4000), 
 CONSTRAINT [PK_INT_Dates] PRIMARY KEY CLUSTERED 
(
	[The_Date] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
      

declare @v_date datetime;
set @v_date = '01/01/2000';
SET DATEFIRST 1;

WHILE @v_date <= '12/31/2020'
BEGIN
   INSERT INTO [dbo].[INT_Dates]
   select @v_date
      ,DATENAME(MM, @v_date) + RIGHT(CONVERT(VARCHAR(12), @v_date, 107), 9) -- 'Month DD, YYYY '
      ,REPLACE(CONVERT(VARCHAR(11), @v_date, 106), ' ', '-') -- 'DD-MON-YYYY' 
      ,CONVERT(VARCHAR(10), @v_date, 101) -- 'MM/DD/YYYY'
      ,CONVERT(VARCHAR(10), @v_date, 111) -- 'YYYY/MM/DD'
      ,DATEPART(weekday, @v_date) -- Day of the Week (Monday is day 1)
      ,DATEPART(day, @v_date) -- Day of the Month
      ,DATEPART(dayofyear, @v_date) -- Day of the Year
      ,LEFT(DATENAME(weekday, @v_date),3) -- Day abbrevivation
      ,DATENAME(weekday, @v_date) -- Day spelled out
      ,DATEPART(week, @v_date) -- Week of Year number
      ,DATEPART(month, @v_date) -- Month of the Year
      ,LEFT(DATENAME(month, @v_date),3) -- Month abbreviation
      ,DATENAME(month, @v_date) -- Month spelled out
      ,DATEPART(quarter, @v_date) -- Quarter of the Year
      ,DATEPART(year, @v_date) -- Year number
      ,'N' --   [Holiday] [CHAR](1) NULL
      ,CASE WHEN DATEPART(weekday, @v_date) < 6 THEN 'Y' ELSE 'N' END --[Business_Day] [CHAR](1) NULL
      ,''; --[Remark] [varchar](4000)
   
   set @v_date = dateadd(day,1,@v_date);
END;