.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Make a month calendar with sql server

Posted By:Kemal AL GAZZAH       Posted Date: January 26, 2016    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

This scripts explains how to create a month calendar using sql sserver
 

There are many ways to display calendar without the need to write code
Calendar controls exist in DOT NET framework for example and we can add them and use them without the need to write code.

The objective of this code is to explore the possibility of creating a month calendar, then a year calendar using only sql script
The advantage of such approach is that you can later make any customisation you can imagine into the calendar you have created.

We will create a stored procedure called usp_calendar which will take as input a month and a year

How ?

First , we need to calculate how many days are in the month

declare @ed as int      
if @m in (1,3,5,7,8,10,12) set @ed=31        
if @m in (4,6,9,11) set @ed=30        
if @y % 4=0 and @m=2 set @ed=29        
if @y % 4!=0 and @m=2 set @ed=28  

Then we make the the date representing the first day of the month
declare @date1 as datetime        
set @date1='01/'+convert(varchar(2),@m)+'/'+convert(varchar(4),@y)        

Then we calculate the week day of that date

declare @j as int        
set @j=datepart(weekday,@date1)     

Then we declare a table in which we will store the month calendar
declare @table  table("lun" varchar(2),"mar" varchar(2),"mer" varchar(2),"jeu" varchar(2),"ven" varchar(2),"sam" varchar(2),"dim" varchar(2))   

Then according to the week day of the fisrt day we insert the first line into the table as follows

if @j=1 insert into @table values('1','2','3','4','5','6','7')        
if @j=2 insert into @table values(' ','1','2','3','4','5','6')        
if @j=3 insert into @table values(' ',' ','1','2','3','4','5')        
if @j=4 insert into @table values(' ',' ',' ','1','2','3','4')        
if @j=5 insert into @table values(' ',' ',' ',' ','1','2','3')        
if @j=6 insert into @table values(' ',' ',' ',' ',' ','1','2')        
if @j=7 insert into @table values(' ',' ',' ',' ',' ',' ','1')      
 


Then we insert the remaining line as follows



declare @i as int        
declare @dim1 as int        
select @dim1=convert(int,dim) from @table        
set @i=1        
while @i<30        
begin        
insert into @table values(@dim1+@i,@dim1+@i+1,@dim1+@i+2,@dim1+@i+3,@dim1+@i+4,@dim1+@i+5,@dim1+@i+6)        
set @i=@i+7        
end    



Finally, here is the complete code of the stored procedure

CREATE proc usp_calendar(@m int,@y int)        
as        
--kamel gazzah        
--kamelgazzah@gmail.com        
begin        
/*declare @m as int        
declare @y as int**/      
declare @ed as int        
/*set @m=7 set @y=2015*/      
if @m in (1,3,5,7,8,10,12) set @ed=31        
if @m in (4,6,9,11) set @ed=30        
if @y % 4=0 and @m=2 set @ed=29        
if @y % 4!=0 and @m=2 set @ed=28         
declare @date1 as datetime        
set @date1='01/'+convert(varchar(2),@m)+'/'+convert(varchar(4),@y)        
declare @j as int        
set @j=datepart(weekday,@date1)        
declare @table  table("lun" varchar(2),"mar" varchar(2),"mer" varchar(2),"jeu" varchar(2),"ven" varchar(2),"sam" varchar(2),"dim" varchar(2))        
if @j=1 insert into @table values('1','2','3','4','5','6','7')        
if @j=2 insert into @table values(' ','1','2','3','4','5','6')        
if @j=3 insert into @table values(' ',' ','1','2','3','4','5')        
if @j=4 insert into @table values(' ',' ',' ','1','2','3','4')        
if @j=5 insert into @table values(' ',' ',' ',' ','1','2','3')        
if @j=6 insert into @table values(' ',' ',' ',' ',' ','1','2')        
if @j=7 insert into @table values(' ',' ',' ',' ',' ',' ','1')        
declare @i as int        
declare @dim1 as int        
select @dim1=convert(int,dim) from @table        
set @i=1        
while @i<30        
begin        
insert into @table values(@dim1+@i,@dim1+@i+1,@dim1+@i+2,@dim1+@i+3,@dim1+@i+4,@dim1+@i+5,@dim1+@i+6)        
set @i=@i+7        
end        
    
  
  
  
  
if exists(select 1 from @table where lun=@ed)  and exists(select 1 from @table where lun='')    
begin    
update @table set lun='' where lun=@ed    
update @table set lun=@ed where dim='1'    
end    
    
  
  
  
if exists(select 1 from @table where mar=@ed) and exists(select 1 from @table where lun='') and exists(select 1 from @table where mar='')    
begin    
update @table set lun='' where mar=@ed    
update @table set mar='' where mar=@ed    
update @table set lun=@ed-1  where sam=''    
update @table set mar=@ed  where sam=''    
end    
    
    
update @table set lun='' where convert(int,lun)>@ed        
update @table set mar='' where convert(int,mar)>@ed        
update @table set mer='' where convert(int,mer)>@ed        
update @table set jeu='' where convert(int,jeu)>@ed        
update @table set ven='' where convert(int,ven)>@ed        
update @table set sam='' where convert(int,sam)>@ed        
update @table set dim='' where convert(int,dim)>@ed        
delete from @table where lun ='' and mar='' and mer='' and jeu='' and ven='' and sam='' and dim=''      
select * from @table    
    
        
end        
        
        
      
    











     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend