Blue Theme Orange Theme Green Theme Red Theme
 
6 Months Free & No Setup Fees ASP.NET Hosting!
Home | Forums | ASP.NET 2.0 Tutorials | Web Services | How Do I...? | Class Browser | WPF Quick Starts | Advertise with Us
 | Consulting  
Submit an Article Submit a Blog 
Search :       Advanced Search »
Home » Blogs Home » Blog Detail

Create Calander using Sql server Store Procedure

 by Dhaval Patel on Jan 18, 2012

Create Stored procedure for Calendar
Comments: 0 Views: 524 Printable Version 
In this Stored procedure pass year and month (numeric)

 CREATE      procedure [dbo].[CalMonthByYearMonth] (@YEAR INT, @MONTH INT) AS
DECLARE @INPUTDATE DATETIME
DECLARE @DATE DATETIME
DECLARE @LASTDATE DATETIME
DECLARE @MONTHDAYCOUNT INT
DECLARE @COUNT INT
DECLARE @DAY VARCHAR(10)
DECLARE @STARTWEEK INT
DECLARE @CURWEEK INT
DECLARE @STARTMONTH INT
SET @INPUTDATE='01/01/' + CAST(@YEAR AS CHAR(4))
PRINT @INPUTDATE
SET @STARTMONTH=@MONTH
SET @INPUTDATE=DATEADD(MM,@MONTH - 1,@INPUTDATE)

SET @COUNT=1
SET @DATE = DATEADD(d, -(DATEPART(dd, @INPUTDATE) - 1), @INPUTDATE)
SET @LASTDATE=DATEADD(DD,-1,DATEADD(MM,1,@DATE))
SET @MONTHDAYCOUNT=datediff(d, @date, dateadd(m, 1, @date))
SET @STARTWEEK=DATEPART(WEEK,@INPUTDATE)
DECLARE @CURRWEEK INT
DECLARE @CUR INT
CREATE TABLE #TEMP(
WEEK VARCHAR(10),
SUNDAY VARCHAR(10),
MONDAY VARCHAR(10),
TUESDAY VARCHAR(10),
WEDNESDAY VARCHAR(10),
THURSDAY VARCHAR(10),
FRIDAY VARCHAR(10),
SATURDAY VARCHAR(10),
YEARWEEK VARCHAR(10))
DECLARE @wkcount int
DECLARE @weeksinmonth int
DECLARE @EXEC NVARCHAR(2000)
SET @WKCOUNT=1
SET @weeksinmonth=datediff(week, @date, @lastdate) + 1
WHILE @wkcount<= @weeksinmonth
begin
INSERT INTO #TEMP VALUES(@wkcount,'SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', @STARTWEEK + @WKCOUNT - 1)
SET @WKCOUNT=@WKCOUNT + 1
end
WHILE @COUNT<=@MONTHDAYCOUNT
BEGIN
SET @DAY=DATENAME(WEEKDAY,@DATE)
IF @STARTWEEK=DATENAME(WEEK,@DATE)
SET @CURRWEEK=1
ELSE
BEGIN
SET @CUR=DATENAME(WEEK,@DATE)
SET @CURRWEEK=(@CUR-@STARTWEEK)+1
END

SET @EXEC='UPDATE #TEMP SET ' + @DAY + ' =' + CAST(@COUNT AS CHAR(2)) + ' WHERE WEEK=' + CAST(@CURRWEEK AS CHAR(2))+ 'AND WEEK IS NOT NULL'
EXEC SP_EXECUTESQL @EXEC
SET @DATE=DATEADD(DD,1,@DATE)
SET @COUNT=@COUNT + 1
END
UPDATE #TEMP SET SUNDAY=' ' WHERE SUNDAY='SUNDAY'
UPDATE #TEMP SET MONDAY=' ' WHERE MONDAY='MONDAY'
UPDATE #TEMP SET TUESDAY=' ' WHERE TUESDAY='TUESDAY'
UPDATE #TEMP SET WEDNESDAY=' ' WHERE WEDNESDAY='WEDNESDAY'
UPDATE #TEMP SET THURSDAY=' ' WHERE THURSDAY='THURSDAY'
UPDATE #TEMP SET FRIDAY=' ' WHERE FRIDAY='FRIDAY'
UPDATE #TEMP SET SATURDAY=' ' WHERE SATURDAY='SATURDAY'
SELECT Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday --, YEARWEEK
--SELECT Monday, Tuesday, Wednesday, Thursday, Friday, YEARWEEK
FROM #TEMP order by week
DROP TABLE #TEMP



Copy and paste this sp into your sql server then execeute stored procedure  for below criteria.

EXEC CalMonthByYearMonth pass year ,pass month
EXEC CalMonthByYearMonth 2012,1

Comment Request!
Thank you for reading this post. Please post your feedback, question, or comments about this post Here.
 
What do you say about this post? Post a comment here
*Title:
*Comment:
 
Comments not available.
Nevron Gauge for SharePoint
Become a Sponsor

 Blogger's Profile
Age: Not Available
Location:
Title: Developer
Joined: Mar 26, 2010
Education: Bachelors Degree
 More Blogs from this Blogger
No record available
 Latest Blogs
[Video] OnClose Handler
[Video] Storing and Loading the Window and Toolbar position
The Euclidean Algorithm
Swapping Exe Process
How Exe file is Generated by VS2005 C++ Project?
What is Exe
Header files: Multiple Inclusion problem - Solution B
Header files: Multiple Inclusion problem - Solution A
Header files: Multiple Inclusion problem - Reason
Header files: Multiple Inclusion problem
View all »
 Latest Jobs
More ... 
 Latest Interview Questions
More ... 

 © 2012  contents copyright of their authors. Rest everything copyright Mindcracker. All rights reserved.