Populate Calendar table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Populate Calendar table

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-13 : 11:44:23

Hi All,

I need to populate a calendar table which has the following fields:

Calendar Date
Day_Name
Week_Number
Month_Number
Quarter_NUmber
Year_Number
Business_Day

I am using SQL Server 2005 .I was wondering if anyone has a script that I can probably use.

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 12:46:28

calendar table with what dates? from which date to which date?

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-13 : 12:52:57

Hi Visakh16,

I need it from 1990 to 2020.

Thanks,
Petronas.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 13:28:03

[code]CREATE FUNCTION [dbo].[fn_DateDim] (@StartDate smalldatetime, @EndDate smalldatetime)
RETURNS @QueryResults TABLE(
TotalYear nvarchar(15),
Qtr nvarchar(10),
Period nvarchar(15),
CalDay nvarchar(10)
) AS
BEGIN

DECLARE @dtDate smalldatetime

SET @dtDate = @StartDate

WHILE (@dtDate <= @EndDate)
BEGIN
INSERT INTO @QueryResults
SELECT
CASE WHEN Month(@dtDate) IN (10,11,12) THEN
'Total Year ' + convert(nvarchar(15),Year(@dtDate)+1 ) ELSE 'Total Year ' + convert(nvarchar(15),Year(@dtDate)) END ,
CASE WHEN Month(@dtDate) IN (10,11,12) THEN 'Qtr 4 ' +
convert(nvarchar(10),Year(@dtDate)+1)
WHEN Month(@dtDate) IN (1,2,3) THEN 'Qtr 1 ' +
convert(nvarchar(10),Year(@dtDate))
WHEN Month(@dtDate) IN (4,5,6) THEN 'Qtr 2 ' +
convert(nvarchar(10),Year(@dtDate))
ELSE 'Qtr 3 ' + convert(nvarchar(10),Year(@dtDate)) END,
datename(mm, @dtDate) + ' ' + convert(nvarchar(4),Year(@dtDate)),
convert(nvarchar(2),month(@dtDate)) + '/' +
convert(nvarchar(2),day(@dtDate)) + '/' +
convert(nvarchar(4),year(@dtDate))

SET @dtDate = DATEADD(dd,1,@dtDate)
END

RETURN
END

--select * from [dbo].[fn_DateDim] ('19900101 ','20201231')[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:36:07

here you go


;With Date_CTE (Date)
AS
(SELECT '19900101'
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM Date_CTE
WHERE DATEADD(dd,1,Date)<='20201231'
)
INSERT INTO Calendar_date
SELECT [Date],
DATENAME(dd,[Date]),
DATEPART(wk,[Date]),
DATEPART(mm,[Date]),
DATEPART(qq,[Date]),
DATEPART(yy,[Date]),
CASE WHEN DATENAME(dw,[Date]) NOT IN ('Sunday','Saturday') THEN 1 ELSE 0 END
FROM Date_CTE

OPTION (MAXRECURSION 0)

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-17 : 12:35:29

Thanks Sakets2000 & Visakh16..Appreciate your help.

How do I find the correct weeks in the year...

I have

declare @getdate datetime
set @getdate='12/31/2009'
select datepart(wk,@getdate)

It gives me 53..but 53*7 = 371, which is wrong..
I need to enter a field week_num in the table which should be the week number of the year .

Thank you,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:15:42

it gives you 53 which means 31 st Dec happen to be 53 rd week (please note that its not looking for 7 day weeks but looks for week number starting from 1 st jan irrespective of day). 53rd week of 2009 may coincide with 1st week of 2010.

Petronas
Posting Yak Master

134 Posts

Posted - 2009-03-17 : 13:38:31

Thanks Visakh16.. as usual you were very helpful

Thanks for your time,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:40:02

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources