Create a table of dates
This script creates a table and populates it with information about all dates between a (configurable) start date and end date.
It creates a table called MyDates in a database called MyDB in which this is done but the script can be easily edited create the table in whichever DB you want it with whatever name you want.
Just copy and paste into a QA window and run. it won't harm any of your existing DBs.
USE master
GO
IF EXISTS (SELECT * FROM master..sysdatabases WHERE NAME = 'MyDB')
DROP DATABASE SourceDB_JT
GO
CREATE DATABASE MyDB
GO
USE SourceDB_JT
GO
IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = 'MyDates')
CREATE TABLE MyDates
(
DateIDINTNOT NULL
,TheDateDATETIMENOT NULL
,DayOfMonthINTNOT NULL
,DayOfYearINTNOT NULL
,DayOfWeekINTNOT NULL
,DayNameVARCHAR(9)NOT NULL
,WeekOfYearINTNOT NULL
,WeekNameVARCHAR(7)NOT NULL
,MonthOfYearINTNOT NULL
,MonthNameVARCHAR(9)NOT NULL
,QuarterINTNOT NULL
,QuarterNameCHAR(6)NOT NULL
,YearINTNOT NULL
,
)
GO
DECLARE@startdateDATETIME
DECLARE@enddateDATETIME
DECLARE@dateDATETIME
DECLARE@idINT
SET@startdate='1900-01-01' --Change these to
SET@enddate='2050-12-31'--whatever you want
SET@id=0
SET@date=DATEADD(dd, @id, @startdate)
WHILE@date <= @enddate
BEGIN
INSERT INTOMyDates
VALUES (@id--DateID
,@date--TheDate
,DATEPART(dd, @date)--DayOfMonth
,DATEPART(dy, @date)--DayOfYear
,DATEPART(dw, @date)--DayOfWeek
,DATENAME(dw, @date)--DayName
,DATEPART(ww, @date)--WeekOfYear
,'Week ' + DATENAME(ww, @date)--WeekName
,DATEPART(mm, @date)--MonthOfYear
,DATENAME(mm, @date)--MonthName
,DATEPART(qq, @date)--Quarter
,'Q' + DATENAME(qq, @date) + DATENAME(yy, @date)--QuarterName
,DATEPART(yy, @date)--Year
)
SET@id=@id + 1
SET@date=DATEADD(dd, @id, @startdate)
END
GO
--select* from MyDates
