question

bhavana avatar image
bhavana asked

To print a calendar without using Date functions

IF OBJECT_ID ('dbo.Calendar1') IS NOT NULL DROP PROCEDURE dbo.Calendar1 GO CREATE PROCEDURE [dbo].[Calendar1] ( @Month int, @Year int ) AS declare @startdateofMonthYear date, @EnddateofMonthYear Date Set @startdateofMonthYear=(Select cast(@Year as varchar(4)) +'-'+Right('00'+Cast(@month as varchar(2)),2) +'-'+'01') Set @EnddateofMonthYear = (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@startdateofMonthYear)+1,0))); WITH CTE_DatesTable AS ( SELECT @startdateofMonthYear AS [date] UNION ALL SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable WHERE DATEADD(dd, 1, [date]) <= @EnddateofMonthYear ) SELECT [DWDateKey]=[date], [DayDate]=datepart(dd,[date]), [DayOfWeekName]=datename(dw,[date]), [MonthNumber]=DATEPART( MONTH ,[date]), [MonthName]=DATENAME( MONTH , [date]), [Year]=DATEPART(YY,[date]) FROM CTE_DatesTable OPTION (MAXRECURSION 0); go HI, Below is my code, i have used date functions like datepart ,datename andall, without using is there a way to get the output? and i should not store the values in a table and call it also...it is the requirement. In above code i am not storing in a table i am directly accessing, but i have used date function:( Please help
calendar
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Why on earth would you want to NOT use date functions? They are there to make things easier.
0 Likes 0 ·
Interesting. Thanks for sharing, I didn't see that previous post.
0 Likes 0 ·

1 Answer

·
Phil Factor avatar image
Phil Factor answered
You can certainly make a calendar for any month since the year 0001 using the SQL Server Date functions. I suspect that you've missed the fact that the DateTime2 datatype does this for you but you must put the leading zeros in the year. I'm afraid we can't do BC, but then there hasn't been much call for that. Create procedure spCalendar --draw a calendar as a result set. you can specify the month if you wwant @Date datetime2=null--any date within the month that you want to calendarise. /* For Novermber 2013 it gives... Mon Tue Wed Thu Fri Sat Sun ---- ---- ---- ---- ---- ---- ---- 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 eg. spCalendar '1 Jan 0100' Execute spCalendar '1 sep 0001' Execute spCalendar '1 nov 2013' Execute spCalendar '28 feb 2008' Execute spCalendar '1 mar 1949' Execute spCalendar '10 jul 2020' */ as Set nocount on --nail down the start of the week Declare @MonthLength int --number of days in the month Declare @MonthStartDW int --the day of the week that the month starts on --if no date is specified, then use the current date Select @Date='01 '+substring(convert(char(11),coalesce(@date,GetDate()),113),4,8) --get the number of days in the month and the day of the week that the month starts on Select @MonthLength=datediff(day,convert(char(11),@Date,113),convert(char(11),DateAdd(month,1,@Date),113)), @MonthStartDW=((Datepart(dw,@date)+@@DateFirst-3) % 7)+1 Select [Mon]=max(case when day=1 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end), [Tue]=max(case when day=2 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end), [Wed]=max(case when day=3 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end), [Thu]=max(case when day=4 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end), [Fri]=max(case when day=5 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end), [Sat]=max(case when day=6 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end), [Sun]=max(case when day=7 and monthdate between 1 and @MonthLength then convert(char(2),monthdate) else '' end) from (--roll out the day number and week number so you can then do a pivot table of the results Select [day]=DayNo.Number, [week]=Weekno.number, [monthDate]=(DayNo.Number + ((Weekno.number-1)*7))-@MonthStartDW from (VALUES (1),(2),(3),(4),(5),(6),(7)) AS DayNo(number) cross join (VALUES (1),(2),(3),(4),(5),(6)) AS Weekno(number) )f group by [week]--so that each week is on a different row having max(case when day=1 and monthdate between 1 and @MonthLength then monthdate else 0 end)>0 or (week=1 and sum(MonthDate)>-21) --take out any weeks on the end without a valid day in them!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.