CALANDER IN SQL SERVER - 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

Script Library

CALANDER IN SQL SERVER

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-18 : 08:23:26

Binu submitted "I had created one calander in SQLSERVER using Functions.Function shows below.
the function execute in this way
SELECT * FROM calander(2,2002)
The OutPut is

SUN MON TUE WED THU FRI SAT
---- ---- ---- ---- ---- ---- ----
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28

(5 row(s) affected)

Copying this function and execute in Query Analyser.
Any month and any year will be passing this function which to get the above output.

CREATE function calander(@month int,@year int)
returns @DAY1 table(SUN char(3),MON Char(3),TUE Char(3),WED Char(3),THU Char(3),FRI Char(3),SAT Char(3))
as
begin
declare @i int
declare @j int
declare @intchk int
declare @dnum int
declare @curdate int
declare @month1 char(2)
declare @year1 char(4)
declare @date char(2)
declare @dtchk int
declare @dtval int
set @date='01'
set @month1=@month
set @year1=@year
set @dtchk=1
set @i=1
set @j=1
declare @DAY2 table(SUN char(3) default '',MON Char(3)default '',TUE Char(3)default '',WED Char(3)default '',THU Char(3)default '',FRI Char(3)default '',SAT Char(3)default '')
SELECT @curdate=DATEPART(dw, CONVERT(DATETIME,@date+'-'+@month1+'-'+@year1,103))
select @dnum=datediff(dd,convert(datetime,@date+'-'+@month1+'-'+@year1,103),dateadd(mm,1,convert(datetime,@date+'-'+@month1+'-'+@year1,103)))
while @j<=7
begin
if @curdate=@j
begin
if @j=1
begin
INSERT INTO @DAY2(sun)VALUES(@i)
set @intchk=1
set @dtchk=1
end
else if @j=2
begin
INSERT INTO @DAY2(MON)VALUES(@i)
set @intchk=2
set @dtchk=1
end
else if @j=3
begin
INSERT INTO @DAY2(TUE)VALUES(@i)
set @intchk=3
set @dtchk=1
end
else if @j=4
begin
INSERT INTO @DAY2(WED)VALUES(@i)
set @intchk=4
set @dtchk=1
end
else if @j=5
begin
INSERT INTO @DAY2(THU)VALUES(@i)
set @intchk=5
set @dtchk=1
end
else if @j=6
begin
INSERT INTO @DAY2(FRI)VALUES(@i)
set @intchk=6
set @dtchk=1
end
else if @j=7
begin
INSERT INTO @DAY2(SAT)VALUES(@i)
set @intchk=7
set @dtchk=1
end

end
set @j=@j+1
end
if @intchk=1
begin
update @day2 set mon=@i+1,tue=@i+2,wed=@i+3,thu=@i+4,fri=@i+5,sat=@i+6 where sun=1
set @dtchk=@dtchk+6
end
else if @intchk=2
begin
update @day2 set tue=@i+1,wed=@i+2,thu=@i+3,fri=@i+4,sat=@i+5 where mon=1
set @dtchk=@dtchk+5
end
else if @intchk=3
begin
update @day2 set wed=@i+1,thu=@i+2,fri=@i+3,sat=@i+4 where tue=1
set @dtchk=@dtchk+4
end
else if @intchk=4
begin
update @day2 set thu=@i+1,fri=@i+2,sat=@i+3 where wed=1
set @dtchk=@dtchk+3
end
else if @intchk=5
begin
update @day2 set fri=@i+1,sat=@i+2 where thu=1
set @dtchk=@dtchk+2
end
else if @intchk=6
begin
update @day2 set sat=@i+1 where fri=1
set @dtchk=@dtchk+1
end

else if @intchk=7
begin
Set @dtchk=@dtchk
end

insert into @day2(sun)values(@dtchk+1)
set @dtchk=@dtchk+1
if @intchk=1
begin
update @day2 set mon=@i+8,tue=@i+9,wed=@i+10,thu=@i+11,fri=@i+12,sat=@i+13 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=2
begin
update @day2 set mon=@i+7,tue=@i+8,wed=@i+9,thu=@i+10,fri=@i+11,sat=@i+12 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=3
begin
update @day2 set mon=@i+6,tue=@i+7,wed=@i+8,thu=@i+9,fri=@i+10,sat=@i+11 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @intchk=4
begin
update @day2 set mon=@i+5,tue=@i+6,wed=@i+7,thu=@i+8,fri=@i+9,sat=@i+10 where sun=@dtchk
set @dtchk=@dtchk+6
end
else if @in

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-18 : 08:24:13

This was posted in ASK SQL Team and was cut off due to the 4,000 character limit. If you see this could you please post the entire function? Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-18 : 13:29:38

With Code Tags


The OutPut is

SUN  MON  TUE  WED  THU  FRI  SAT  
---- ---- ---- ---- ---- ---- ---- 
                         1    2  
3    4    5    6    7    8    9  
10   11   12   13   14   15   16 
17   18   19   20   21   22   23 
24   25   26   27   28           

(5 row(s) affected)

When you post AskSQLTeam...don't you have to supply an email address?

I'm gonna go test it...

Brett

8-)

Hunglech
Starting Member

16 Posts

Posted - 2005-07-29 : 21:40:08

I had created the same in SQLSERVER using Stored Procedures

CREATE PROCEDURE xs_Calender @nMonth TINYINT, @nYear INT
AS
BEGIN
DECLARE @dFrom SMALLDATETIME, @dTo SMALLDATETIME, @dFirst SMALLDATETIME, @dCur SMALLDATETIME
DECLARE @strSQL NVARCHAR(4000)
DECLARE @i INT, @j INT, @k INT, @n INT
DECLARE @cChar CHAR(2)

SET @dFrom = REPLACE(STR(@nYear, 4), ' ', '0') + REPLACE(STR(@nMonth, 2), ' ', '0') + '01'
SELECT @i = 0, @n = 6, @dTo = DATEADD(DAY, -1, DATEADD(MONTH, 1, @dFrom)), @k = DATEPART(WEEKDAY, @dFrom)
IF @k = 1 SET @k = 8
SET @k = @k-1
SET @dFirst = DATEADD(DAY, -1*@k , @dFrom)
IF DATEADD(DAY, 35, @dFirst) >= @dTo SET @n = 5
WHILE @i < @n
BEGIN
IF @i = 0 SET @strSQL = 'SELECT ' ELSE SET @strSQL = @strSQL + CHAR(13) + 'UNION ALL SELECT '
SET @j = 0
WHILE @j < 7
BEGIN
SET @dCur = DATEADD(DAY, @i*7+@j+1, @dFirst)
SELECT @cChar = ''
IF @dCur BETWEEN @dFrom AND @dTo SET @cChar = STR(DAY(@dCur), 2)
SET @strSQL = @strSQL + CASE WHEN @j = 0 THEN '' ELSE ',' END + CHAR(39) + @cChar + CHAR(39)
IF @i = 0
SELECT @strSQL = + @strSQL + ' AS ' +
CASE
WHEN @j = 0 THEN 'Mon'
WHEN @j = 1 THEN 'Tue'
WHEN @j = 2 THEN 'Wed'
WHEN @j = 3 THEN 'Thu'
WHEN @j = 4 THEN 'Fri'
WHEN @j = 5 THEN 'Sat'
WHEN @j = 6 THEN 'Sun'
END
SET @j = @j + 1
END
SET @i = @i + 1
END
EXEC sp_executesql @strSQL
END
GO

EXEC xs_Calender 7, 2005
The otuput it
Mon
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-01 : 01:36:20

Hunglech
Simple and neat code.
If you change SmallDateTime to DateTime then, it is possible to generate calenders from year 1753 to 9999

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-19 : 08:00:48

I couldn't resist writing another one...

--input
declare @d datetime
set @d = '20060401'

--calculation
declare @DayOfFirstSundayOfMonth tinyint
set @DayOfFirstSundayOfMonth = (15 - (datepart(dw, dateadd(mm, datediff(mm, 0, @d), 0)) + @@datefirst)) % 7 + 1

declare @LastDayOfMonth tinyint
set @LastDayOfMonth = datepart(day, dateadd(mm, datediff(mm, -1, @d), -1))

declare @numbers table (i tinyint identity(1, 1), j bit)
insert @numbers select top 31 null from master.dbo.syscolumns

declare @x varchar(105)
set @x = ''
select @x = @x + cast(i as char(3)) from @numbers
set @x = replicate('   ', @DayOfFirstSundayOfMonth-1) + left(@x, 3 * @LastDayOfMonth)

select substring(x, 1, 3) as 'Sun',
       substring(x, 4, 3) as 'Mon',
       substring(x, 7, 3) as 'Tue',
       substring(x, 10, 3) as 'Wed',
       substring(x, 13, 3) as 'Thu',
       substring(x, 16, 3) as 'Fri',
       substring(x, 19, 3) as 'Sat'
from (        select substring(@x, 1, 21) x
    union all select substring(@x, 21 + 1, 21)
    union all select substring(@x, 21 * 2 + 1, 21)
    union all select substring(@x, 21 * 3 + 1, 21)
    union all select substring(@x, 21 * 4 + 1, 21)
    ) a

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-19 : 08:59:13

Good Ryan, you play very well with SQL

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-19 : 09:23:13

Ryan, it doesnt show the correct calander for the date given('20060401')

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-19 : 09:28:19

LOL - you're right.

I'll fix it...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-19 : 09:41:10

A few minor alterations (in red). Must learn to check what I write!

--input
declare @d datetime
set @d = '20060401'

--calculation
declare @DayOfFirstSundayOfMonth tinyint
set @DayOfFirstSundayOfMonth = (15 - (datepart(dw, dateadd(mm, datediff(mm, 0, @d), 0)) + @@datefirst)) % 7 + 1

declare @LastDayOfMonth tinyint
set @LastDayOfMonth = datepart(day, dateadd(mm, datediff(mm, -1, @d), -1))

declare @numbers table (i tinyint identity(1, 1), j bit)
insert @numbers select top 31 null from master.dbo.syscolumns

declare @x varchar(120)
set @x = ''
select @x = @x + cast(i as char(3)) from @numbers
set @x = replicate('   ', (8-@DayOfFirstSundayOfMonth) % 7) + left(@x, 3 * @LastDayOfMonth)

select substring(x, 1, 3) as 'Sun',
       substring(x, 4, 3) as 'Mon',
       substring(x, 7, 3) as 'Tue',
       substring(x, 10, 3) as 'Wed',
       substring(x, 13, 3) as 'Thu',
       substring(x, 16, 3) as 'Fri',
       substring(x, 19, 3) as 'Sat'
from (        select substring(@x, 1, 21) x
    union all select substring(@x, 21 + 1, 21)
    union all select substring(@x, 21 * 2 + 1, 21)
    union all select substring(@x, 21 * 3 + 1, 21)
    union all select substring(@x, 21 * 4 + 1, 21)
    union all select substring(@x, 21 * 5 + 1, 21)    ) a

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-04-19 : 12:45:49

Brilliant Work!!!

________________
©ode is snatched

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-20 : 01:54:40

Yes. Simple and perfect

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-20 : 20:46:10

It’s a lot easier to do with a date table. The script below prints a formatted calendar for the year 2006.

[shameless self promotion]

You can get my World Famous, Mother of All Date Tables function, F_TABLE_DATE, at this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

[/shameless self promotion]


-- Generate a Formatted Calendar for a Date Range
select
	[Month] = max(YEAR_MONTH_NAME),
	[ Sun] = max(case when WD = 1 then WDC else BK end),
	[ Mon] = max(case when WD = 2 then WDC else BK end),
	[ Tue] = max(case when WD = 3 then WDC else BK end),
	[ Wed] = max(case when WD = 4 then WDC else BK end),
	[ Thu] = max(case when WD = 5 then WDC else BK end),
	[ Fri] = max(case when WD = 6 then WDC else BK end),
	[ Sat] = max(case when WD = 7 then WDC else BK end)
from
	(
	select
		YEAR_MONTH,
		YEAR_MONTH_NAME,
		WEEK_STARTING_SUN_SEQ_NO,
		WD = DAY_OF_WEEK,
		WDC = right('    '+right(DAY_OF_MONTH,2),4),
		BK = convert(varchar(4),'    ')
	from
		-- From and To dates in function parameters
		dbo.F_TABLE_DATE('20060101','20061231')
	) a
GROUP BY
	YEAR_MONTH,
	WEEK_STARTING_SUN_SEQ_NO
ORDER BY
	YEAR_MONTH,
	WEEK_STARTING_SUN_SEQ_NO

Results:


Month     Sun  Mon  Tue  Wed  Thu  Fri  Sat 
-------- ---- ---- ---- ---- ---- ---- ---- 
2006 Jan    1    2    3    4    5    6    7
2006 Jan    8    9   10   11   12   13   14
2006 Jan   15   16   17   18   19   20   21
2006 Jan   22   23   24   25   26   27   28
2006 Jan   29   30   31                    
2006 Feb                   1    2    3    4
2006 Feb    5    6    7    8    9   10   11
2006 Feb   12   13   14   15   16   17   18
2006 Feb   19   20   21   22   23   24   25
2006 Feb   26   27   28                    
2006 Mar                   1    2    3    4
2006 Mar    5    6    7    8    9   10   11
2006 Mar   12   13   14   15   16   17   18
2006 Mar   19   20   21   22   23   24   25
2006 Mar   26   27   28   29   30   31     
2006 Apr                                  1
2006 Apr    2    3    4    5    6    7    8
2006 Apr    9   10   11   12   13   14   15
2006 Apr   16   17   18   19   20   21   22
2006 Apr   23   24   25   26   27   28   29
2006 Apr   30                              
2006 May         1    2    3    4    5    6
2006 May    7    8    9   10   11   12   13
2006 May   14   15   16   17   18   19   20
2006 May   21   22   23   24   25   26   27
2006 May   28   29   30   31               
2006 Jun                        1    2    3
2006 Jun    4    5    6    7    8    9   10
2006 Jun   11   12   13   14   15   16   17
2006 Jun   18   19   20   21   22   23   24
2006 Jun   25   26   27   28   29   30     
2006 Jul                                  1
2006 Jul    2    3    4    5    6    7    8
2006 Jul    9   10   11   12   13   14   15
2006 Jul   16   17   18   19   20   21   22
2006 Jul   23   24   25   26   27   28   29
2006 Jul   30   31                         
2006 Aug              1    2    3    4    5
2006 Aug    6    7    8    9   10   11   12
2006 Aug   13   14   15   16   17   18   19
2006 Aug   20   21   22   23   24   25   26
2006 Aug   27   28   29   30   31          
2006 Sep                             1    2
2006 Sep    3    4    5    6    7    8    9
2006 Sep   10   11   12   13   14   15   16
2006 Sep   17   18   19   20   21   22   23
2006 Sep   24   25   26   27   28   29   30
2006 Oct    1    2    3    4    5    6    7
2006 Oct    8    9   10   11   12   13   14
2006 Oct   15   16   17   18   19   20   21
2006 Oct   22   23   24   25   26   27   28
2006 Oct   29   30   31                    
2006 Nov                   1    2    3    4
2006 Nov    5    6    7    8    9   10   11
2006 Nov   12   13   14   15   16   17   18
2006 Nov   19   20   21   22   23   24   25
2006 Nov   26   27   28   29   30          
2006 Dec                             1    2
2006 Dec    3    4    5    6    7    8    9
2006 Dec   10   11   12   13   14   15   16
2006 Dec   17   18   19   20   21   22   23
2006 Dec   24   25   26   27   28   29   30
2006 Dec   31                              

(63 row(s) affected)

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-20 : 21:08:57

quote:
"You can get my World Famous, Mother of All Date Tables function, F_TABLE_DATE"

Honestly it is a very useful function

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 03:09:35

>>World Famous, Mother of All Date Tables function, F_TABLE_DATE

No doubt on that

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-21 : 05:05:08

quote:
Originally posted by Michael Valentine Jones

It’s a lot easier to do with a date table.

Yeah, but that's cheating!

When there is something as useful as your famous date table, I think shameless self-promotion is permitted.

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-24 : 12:33:10

A one year calendar with a little nicer formatting.


declare @from datetime, @to datetime
select @from = '20050101', @to = '20051231'

select
	[          Calendar] = [CAL_LINE]
from
(
select
	a.YEAR_MONTH,
	a.SEQ,
	[CAL_LINE] =
	max(case when WD = 1 then WDC else BK end)+' '+
	max(case when WD = 2 then WDC else BK end)+' '+
	max(case when WD = 3 then WDC else BK end)+' '+
	max(case when WD = 4 then WDC else BK end)+' '+
	max(case when WD = 5 then WDC else BK end)+' '+
	max(case when WD = 6 then WDC else BK end)+' '+
	max(case when WD = 7 then WDC else BK end)
from
	(
	select
		YEAR_MONTH,
		YEAR_MONTH_NAME,
		SEQ = WEEK_STARTING_SUN_SEQ_NO,
		WD = DAY_OF_WEEK,
		WDC = right('    '+right(DAY_OF_MONTH,2),3),
		BK = convert(varchar(3),'    ')
	from
		-- From and To dates in function parameters
		dbo.F_TABLE_DATE(@from,@to)
	) a
GROUP BY
	YEAR_MONTH,
	SEQ
UNION ALL
select
	b1.YEAR_MONTH,
	b2.SEQ,
	[CAL_LINE] =
	max(
	right(
	case b2.SEQ 
	when 999998 then ''
	when 999999 then ''
	when 2 then replicate(' ',ceiling(11-(len(b1.MONTH_NAME_LONG)/2.)))+
			b1.MONTH_NAME_LONG+' '+b1.YEAR_NAME
	when 3 then ''
	when 4 then 'Sun Mon Tue Wed Thu Fri Sat'
	when 5 then '--- --- --- --- --- --- ---' else '' end,27))
from
	-- From and To dates in function parameters
	dbo.F_TABLE_DATE(@from,@to) b1
	CROSS JOIN
	( select SEQ = 999998 union select 999999 union select 2 union
	 select 3 union select 4  union select 5 ) b2
group by
	b1.YEAR_MONTH, b2.SEQ
) aa
ORDER BY
	aa.YEAR_MONTH,
	aa.SEQ

Results:


          Calendar          
--------------------------- 
        January 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
                          1
  2   3   4   5   6   7   8
  9  10  11  12  13  14  15
 16  17  18  19  20  21  22
 23  24  25  26  27  28  29
 30  31                    


       February 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
          1   2   3   4   5
  6   7   8   9  10  11  12
 13  14  15  16  17  18  19
 20  21  22  23  24  25  26
 27  28                    


         March 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
          1   2   3   4   5
  6   7   8   9  10  11  12
 13  14  15  16  17  18  19
 20  21  22  23  24  25  26
 27  28  29  30  31        


         April 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
                      1   2
  3   4   5   6   7   8   9
 10  11  12  13  14  15  16
 17  18  19  20  21  22  23
 24  25  26  27  28  29  30


          May 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
  1   2   3   4   5   6   7
  8   9  10  11  12  13  14
 15  16  17  18  19  20  21
 22  23  24  25  26  27  28
 29  30  31                


         June 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
              1   2   3   4
  5   6   7   8   9  10  11
 12  13  14  15  16  17  18
 19  20  21  22  23  24  25
 26  27  28  29  30        


         July 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
                      1   2
  3   4   5   6   7   8   9
 10  11  12  13  14  15  16
 17  18  19  20  21  22  23
 24  25  26  27  28  29  30
 31                        


        August 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
      1   2   3   4   5   6
  7   8   9  10  11  12  13
 14  15  16  17  18  19  20
 21  22  23  24  25  26  27
 28  29  30  31            


       September 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
                  1   2   3
  4   5   6   7   8   9  10
 11  12  13  14  15  16  17
 18  19  20  21  22  23  24
 25  26  27  28  29  30    


        October 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
                          1
  2   3   4   5   6   7   8
  9  10  11  12  13  14  15
 16  17  18  19  20  21  22
 23  24  25  26  27  28  29
 30  31                    


       November 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
          1   2   3   4   5
  6   7   8   9  10  11  12
 13  14  15  16  17  18  19
 20  21  22  23  24  25  26
 27  28  29  30            


       December 2005

Sun Mon Tue Wed Thu Fri Sat
--- --- --- --- --- --- ---
                  1   2   3
  4   5   6   7   8   9  10
 11  12  13  14  15  16  17
 18  19  20  21  22  23  24
 25  26  27  28  29  30  31



(135 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources