Comments posted to this topic are about the item Print The Calender 2

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Nice thanks added it to my SANDBOX for future use

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

You are a bit US centric... ;-) Should have "SET DATEFIRST 7" in there if you want to make sure that your script runs correctly everywhere (or take @@DATEFIRST into account when identifiying the given day of week).

A calender is a series of hard pressure rollers used to form or smooth a sheet of material (see http://en.wikipedia.org/wiki/Calender).

Not sure how this relates to dates.

^^^^^
HA! That's exactly what I was thinking! CALENDAR <> CALENDER
(Maybe he drives around in a Camero, too...)

Another way to print a calendar using PIVOT:

CREATE FUNCTION CalenderUsingPivot
(
   @BeginDate date
   , @EndDate date
)
RETURNS @Calendar TABLE
(
   Week tinyint
   , Monday tinyint
   , Tuesday tinyint
   , Wednesday tinyint
   , Thursday tinyint
   , Friday tinyint
   , Saturday tinyint
   , Sunday tinyint
)
AS
BEGIN
   DECLARE @date TABLE
   (
      Date date
      , Day AS DAY(Date)
      , Week AS DATEPART(week, Date)
      , Weekday AS DATENAME(weekday, Date)
   );
   
   DECLARE @i date;

   SET @i = @BeginDate;

   WHILE @i <= @EndDate
   BEGIN
      INSERT @date VALUES (@i);
      SET @i = DATEADD(day, 1, @i);
   END;
   
   INSERT @Calendar
   SELECT   
      Week
      , p.Monday
      , p.Tuesday
      , p.Wednesday
      , p.Thursday
      , p.Friday
      , p.Saturday
      , p.Sunday
   FROM 
   (
      SELECT Day, Week, Weekday 
      FROM @date
   ) AS c
   PIVOT 
   (
      MAX(Day) FOR Weekday IN
      (
         Monday
         , Tuesday
         , Wednesday
         , Thursday
         , Friday
         , Saturday
         , Sunday
      )
   ) AS p
   ORDER BY Week;

   RETURN;
END;

GO

SET LANGUAGE English;

SELECT *
FROM dbo.CalenderUsingPivot('2012-01-01', '2012-12-31');

I really liked your calendar idea, so I amended it a bit for my use..

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[calendar2print]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].calendar2print
GO

/***************************************************************************************
Created By :- Vinay Kumar
Created on :- 29 Jan 2008
   EXEC calender '01/01/2016'
Purpose :- Print the calender from the given Date until end of current year
------------------------------------
Amended by Hannes Malan
Amended On :- Aug 13, 2012
   EXEC calendar2print @StartDate=null, @fullmonth=1, @months=1
Purpose :- Print the calender from the given Date ...
         ..and if @StartDate is null, use current date               (@StartDate)
         ..also from specified date or from first day of specified month (@fullmonth)
         ..for a certain amount of months                        (@months)
****************************************************************************************/

Create Procedure calendar2print 
    @StartDate DateTime 
   ,@fullmonth int = 1
   ,@months int = 1
As
set nocount on

if @StartDate is null set @StartDate = getdate()

if (@fullmonth = 1) begin
   set @StartDate  = '01 ' + datename(mm,@StartDate) +' '+ datename(yy,@StartDate)
end

set datefirst 7
DECLARE @tbl Table(RowID int identity(1,1),  Sun varchar(20) default '', Mon varchar(20) default '', Tue varchar(20) default '', 
               Wed varchar(20) default '', Thu varchar(20) default '', Fri varchar(20) default '', Sat varchar(20) default '')

declare @monthCount int 
set @monthCount = 1
DECLARE @currentMonth int
DECLARE @CurrentDate int
DECLARE @CurrentDay int
DECLARE @CurrentRowID int
DECLARE @TotalMonthDays int
DECLARE @monthName varchar(20)


set @CurrentDate = datepart(dd, @StartDate)
set @currentMonth = datepart(mm, @StartDate)
set @CurrentDay = datepart(dw, @StartDate)

insert @tbl(Mon) values ('')
set @CurrentRowID = @@Identity

while @monthCount <= @months begin
   set @monthCount = @monthCount + 1
    set @TotalMonthDays = case when month(@StartDate) in (1,3,5,7,8,10,12) then 31
                     when month(@StartDate) in (4,6,9,11) then 30
                     when month(@StartDate) = 2 and year(@StartDate) % 4 = 0 then 29
                     when month(@StartDate) = 2 and year(@StartDate) % 4 <> 0 then 28
                     end
   
   set @monthName = datename(m, @StartDate)
   insert @tbl(Tue, Thu) values (@monthName, Convert(Varchar, year(@StartDate)))
   set @CurrentRowID = @@Identity

   insert @tbl(Sun,Mon,Tue,Wed, Thu, Fri,Sat) values ('_______', '_______', '_______', '_______', '_______', '_______', '_______')

   insert @tbl(Sun) values ('')
   set @CurrentRowID = @@Identity

   while @CurrentDate <= @TotalMonthDays begin
      if @CurrentDay = 1 begin
         insert @tbl(Sun) values ('')
         set @CurrentRowID = @@Identity
      end
      if @CurrentDay = 1 
         update @tbl set Sun = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID       
      else if @CurrentDay = 2 
         update @tbl set Mon = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
      else if @CurrentDay = 3 
         update @tbl set Tue = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
      else if @CurrentDay = 4 
         update @tbl set wed = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
      else if @CurrentDay = 5 
         update @tbl set Thu = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
      else if @CurrentDay = 6 
         update @tbl set Fri = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
      else if @CurrentDay = 7 
         update @tbl set Sat = Convert(Varchar(2), @CurrentDate) where RowID = @CurrentRowID
            
      
      set @CurrentDate = @CurrentDate + 1
      if @CurrentDate = @TotalMonthDays + 1 begin
         IF (Datepart(DD,@StartDate)>1)
            set @StartDate = DATEADD(Day,-(Datepart(DD,@StartDate)-1),@StartDate)
         set @StartDate =  DATEADD(Month,1,@StartDate)
         set @CurrentDay = datepart(dw, @StartDate)
         insert @tbl(Sun,Mon,Tue,Wed,Thu,Fri,Sat) 
         values ('*******', '*******', '*******', '*******', '*******', '*******', '*******')
      end else begin
         set @CurrentDay = (@CurrentDay + 1) % 8
         if (@CurrentDay = 0) set @CurrentDay = 1
      end 
   end
   set @CurrentDate = 1
end


-- Finaly show the output
select Sun, Mon, Tue, Wed, Thu, Fri, Sat 
from @tbl 
where  Mon <> '' or Tue <> '' or Wed <> ''  
   or Thu <> '' or Fri <> '' or Sat <> '' or Sun <> ''

I have to ask... what will you actually end up using this for? Not challenging what you're doing. I just really want to know.

--Jeff Moden

---

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs

hehehe.. , so far ...I have no idea...

I just like the odd script that is off the beaten track
:-P

to expound a bit further...
...one might not use the script it was written for, but at times you get to a situation where you need similar logic to solve some kind of bussiness need.

One issue - the Leap Day calculation is missing one constraint - February does not have 29 days in Centuries not divisible by 400, ie 2100 will be 28 days, but 2000 was 29 days.

Otherwise the script looks like it works like a charm...