question

bhavana avatar image
bhavana asked

To print a Calendar in sql server 2008

Hi , Below is the code i am using to get a calendar based on the year and month entered, I want a code like it should accept whichever month i give, like 200BC or 1000 year, is there any other code or below code can be modified??An i should not store those values in a table and retrive data from it. Please help.. declare @datemonth int = 1, @dateYear int = 2014, @startdateofMonthYear date, @EnddateofMonthYear Date Set @startdateofMonthYear=(Select cast(@dateYear as varchar(4)) +'-'+Right('00'+Cast(@datemonth 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]),DATEPART(weekday,[date]), [DayOfWeekName]=datename(dw,[date]),[WeekNumber]=DATEPART( WEEK , [date]),[MonthNumber]=DATEPART( MONTH , [date]),[MonthName]=DATENAME( MONTH , [date]),[Year]=DATEPART(YY,[date]), [QuarterNumber]=DATENAME(quarter, [date]) FROM CTE_DatesTable OPTION (MAXRECURSION 0); go
calendar
1 comment
10 |1200

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

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
There is no native datatype that will support dates before year 0001. You will get an error with your code if you try any date prior to 1753, as the date function dateadd returns a datetime datatype, which is limited to 1753-9999. I don't know how you can programatically do this as the 'calendar' has undergone so many changes. For example, prior to 45BC, when the Julian calendar was introduced, the months of January, April, June, August, September, November and December all had a differnt number of days to what they do now. Prior to 713BC, there were only 10 months. And then you've got the 7-day week - that only came into the Roman calendar between the 1st and 3rd centuries. You can see now why SQL datetime only supports 1753 onwards - the first full year the Gregorian calendar was adopted in Britain. You would have to use values from a table in my opinion.
10 |1200

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

Squirrel avatar image
Squirrel answered
if you change this portion, your calendar should be able to support year 1 to 9999. set @startdateofMonthYear = dateadd(month, @datemonth - 1, dateadd(year, @dateYear - 1, convert(date, '0001-01-01'))) set @EnddateofMonthYear = dateadd(day, -1, dateadd(month, 1, @startdateofMonthYear))
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.