Suppose we need to find out the name of all the months in a given year (Year will be given as an input parameter) where first day of the month falls on weekend (Saturday or Sunday).
So what should be the query/stored procedure to get the above result?
create proc techmixing_8_4_14 @yr as numeric
as
SET NOCOUNT ON;
set datefirst 1 — to set first day as monday
declare @dt as varchar(8);
declare @count as numeric=1
create table #t1 (mn varchar(20)) — Temporary table
while @count <=12
begin
set @dt=convert(varchar(4),@yr ) + isnull(REPLICATE('0',2-len(convert(varchar(2),@count))),'')+convert(varchar(2),@count)+ '01'
set @count=@count+1
if datepart(dw,convert(date,@dt,112 )) >=6
begin
insert into #t1 (mn) select datepart(MM,@dt)
end
end
select * from #t1
go
exec techmixing_8_4_14 2012
To get month name, we can also replace datepart with datename in below statement :
insert into #t1 (mn) select datepart(MM,@dt)
Really Good Answer Bharat.. Keep it up
Just to little modify the Bharat answer by adding "No count off" in the end.
Create proc techmixing_8_4_14 @yr as numeric
as
begin
SET NOCOUNT ON;
set datefirst 1 — to set first day as monday
declare @dt as varchar(8);
declare @count as numeric=1
create table #t1 (mn varchar(20)) — Temporary table
while @count <=12
begin
set @dt=convert(varchar(4),@yr ) + isnull(REPLICATE('0',2-len(convert(varchar(2),@count))),'')+convert(varchar(2),@count)+ '01'
set @count=@count+1
if datepart(dw,convert(date,@dt,112 )) >=6
begin
insert into #t1 (mn) select datename(MM,@dt)
end
end
select * from #t1
set nocount off
end
go
exec techmixing_8_4_14 2012
Declare @Months Varchar(60)
Declare @loop int
SET @loop=1
WHILE @loop<=12
BEGIN
IF(Datename(dw,'2014/'+str(@loop)+'/1') in ('Saturday','Sunday'))
BEGIN
SET @Months=COALESCE(@Months+ ', ', '')+ DateName( month , DateAdd( month , @loop , 0 ) -1)
END
SET @loop=@loop+1
END
Print @Months
Pingback: pressure washers in charlotte nc
Pingback: convert safaricom airtime to mpesa
Pingback: อนิเมะฮาเร็ม
Pingback: vegus
Pingback: How to find 100X 500X Gains in Gold Silver Jr Mining Stocks
Pingback: รีไฟแนนซ์รถมอเตอร์ไซค์
Pingback: http://apartments-parami.pl/bezpieczenstwo-miedzynarodowe-181.php
Pingback: http://amsico.pl/nasladuje-zwierzaki-2-latek-umie-161.php
Pingback: link vao w88
Pingback: w88th
Pingback: Empire Market
Pingback: fun88asia
Pingback: Dream Market
Pingback: Crystal Meth kaufen
Pingback: cbdque
Pingback: Empire Market
Pingback: Tochka Market
Pingback: dang ky v9bet
Pingback: keluaran hkg
Pingback: cdrqq
Pingback: 2020 Cover Economist
Pingback: mega poker99
Pingback: www.1lapakqq.site
Pingback: sbobet888
Pingback: poker 99