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