February 16, 2012 at 5:14 am
Hi can any one help how to write the procedure in tsql if i pass any year it has to generate that year calender as in the format i need to get the result set as with full calender of that year.
date_id day_date weekday_flg week_in_year month_in_year days_in_month quarter_in_year year
1 2011-01-01 N Saturday January 31 1 2011
if it is weekday it has to be Y weekend means N
February 16, 2012 at 5:52 am
CREATE PROC p_GenYearDays(@year int)
AS
BEGIN
;with d366
as
(
select top 366 CAST(CAST(@year as varchar(4)) + '0101' AS DATETIME) d
from sys.objects s1 cross join sys.objects s2
)
, yd
as
(
select DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, d) d from d366
)
select ROW_NUMBER() OVER (ORDER BY d) as date_id
,d as day_date
,CASE WHEN DATENAME(dw,d) IN ('Sunday','Saturday')
THEN 'N'
ELSE 'Y'
END as weekday_flg
,DATEPART(WEEK,d) as week_in_year
-- the above may need to be twicked depending of what is really requried
-- as it may return 53 weeks, also depends on SET DATEFIRST...
-- i would add another week...
-- read http://msdn.microsoft.com/en-us/library/ms174420.aspx
,DATEPART(ISO_WEEK,d) as iso_week_in_year
,DATENAME(weekday,d) as day_in_week
,DAY(d) as day_in_month
,DATENAME(month,d) as month_in_year
-- it may be done differently:
,DAY(dateadd (day, -1, dateadd(month, month(d), dateadd(year, @year - 1900, 0)))) as days_in_month
,DATEPART(quarter, d) as quarter_in_year
,DATEPART(year, d) as [year]
from yd
where YEAR(d) = @year
END
If the user has no access to sys.objects table, you can prepopulate dedicated table with 366 rows containing anything (eg. numbers from 1 to 366...)
February 16, 2012 at 6:10 am
Thanks for reply it is helpful
February 21, 2012 at 11:23 pm
Hi,
You can try this one.
declare @t table (id int identity(1,1),time_stamp date)
declare @Daysin_Month table (Month_No int, No_of_Days int)
declare @st date,@en date
select @st = '2012-01-01'
select @en = '2012-12-31'
insert into @t
select convert(varchar(10),GETDATE(),110) from sys.objects
/*INFORMATION_SCHEMA.COLUMNS*/
update @t set time_stamp = dateadd(day,
id-(datediff(day,@st,getdate())+1),
time_stamp)
insert into @Daysin_Month
select DATEPART(M,time_stamp) Cur_Mon,COUNT(*) No_of_Days from @t where time_stamp between @st and @en
group by DATEPART(M,time_stamp)
select id,ROW_NUMBER() over (partition by month(time_stamp) order by time_stamp) Current_Day, time_stamp [Date],
DATEPART(weekday,time_stamp) Week_Day,
DATEPART(week,time_stamp) Week_in_Year,
DATENAME(dw,time_stamp) weekname,
case when datepart(WEEKDAY,time_stamp) IN (1,7) then 'N' else 'Y' end weekday_flag,
DATEPART(qq,time_stamp) Qtr,
No_of_Days
from @t , @Daysin_Month
where time_stamp between @st and @en
and Month_No = DATEPART(m,time_stamp)
Regards
Siva Kumar J.
February 24, 2012 at 4:21 am
nhimabindhu (2/16/2012)
Hi can any one help how to write the procedure in tsql if i pass any year it has to generate that year calender as in the format i need to get the result set as with full calender of that year.date_id day_date weekday_flg week_in_year month_in_year days_in_month quarter_in_year year
1 2011-01-01 N Saturday January 31 1 2011
if it is weekday it has to be Y weekend means N
My recommendation is that, instead of recalculating this over and over, make a permanent calendar table.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply