DECLARE @StartDate DATETIME
SET @StartDate='01/01/'+DATENAME(Year,GETDATE())
;WITH CTE AS
(
SELECT @StartDate as dt, 1 as Id
UNION ALL
SELECT DATEADD(dd,1,dt),CTE.Id + 1
FROM CTE
WHERE Id <= 365
)
SELECT ROW_NUMBER()OVER(ORDER BY dt) AS ID,
dt AS SATSUNDate,DATENAME(dw,dt) AS [Day]
FROM CTE A
WHERE (DATEPART(dw,dt)=1 OR DATEPART(dw,dt)=7) --this will give you all SAT & SUN List
OPTION (MAXRECURSION 0);
declare @startDate date,@enddate date
set @startDate='01/01/2012'
set @enddate='12/31/2012'
while @startDate<=@enddate
begin
if(DATENAME(dw,@startDate)='Sunday' or DATENAME(dw,@startDate)='Saturday')
insert into MyHoliday(Holiday_date,Specification) values(convert(date,@startDate,103),DATENAME(dw,@startDate))
set @startDate=DATEADD(DD,1,@startDate)
end
if(Hard_Work && Luck)
Response.Write("Success");
Best Of Luck
Shival Thakur
Marked as answer by Arshad Ashraf on Apr 23, 2012 06:31 AM
DECLARE @StartDate DATETIME
SET @StartDate='01/01/2010'
;WITH CTE AS
(
SELECT @StartDate as dt, 1 as Id
UNION ALL
SELECT DATEADD(dd,1,dt),CTE.Id + 1
FROM CTE
WHERE Id <= 365
)
Insert Into HOLIDAY
([HOLIDAY_DATE],[SPECIFICATION])
Values
(
(select dt from CTE Where(DATEPART(dw,dt)=1 OR DATEPART(dw,dt)=7)),
(Select DATENAME(dw,dt)from CTE WHERE (DATEPART(dw,dt)=1 OR DATEPART(dw,dt)=7))
)OPTION (MAXRECURSION 0);
But i Got Some Error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
--you must use this query to insert records
DECLARE @StartDate DATETIME
SET @StartDate='01/01/2010'
;WITH CTE AS
(
SELECT @StartDate as dt, 1 as Id
UNION ALL
SELECT DATEADD(dd,1,dt),CTE.Id + 1
FROM CTE
WHERE Id <= 365
)
Insert Into HOLIDAY
(
[HOLIDAY_DATE],[SPECIFICATION]
)
--insert select query
select dt,DATENAME(dw,dt)
from CTE
Where(DATEPART(dw,dt)=1 OR DATEPART(dw,dt)=7)
OPTION (MAXRECURSION 0)
Arshad Ashra...
Member
620 Points
149 Posts
Sql Query For Getting Sundays And Saturdays in a given Year
Apr 23, 2012 05:30 AM|LINK
SQL Query For Gettings All Saturdays And Sundays In A Given Year And Inset Into My Holiday Table
Table Fields
Holiday_id(pk) int autoincrement
Holiday_date smalldatetime
Specification varchar(50)
Expected Result
yrb.yogi
Star
14460 Points
2402 Posts
Re: Sql Query For Getting Sundays And Saturdays in a given Year
Apr 23, 2012 05:51 AM|LINK
.Net All About
shivalthakur
Participant
1837 Points
531 Posts
Re: Sql Query For Getting Sundays And Saturdays in a given Year
Apr 23, 2012 05:52 AM|LINK
try this
Response.Write("Success");
Best Of Luck
Shival Thakur
Arshad Ashra...
Member
620 Points
149 Posts
Re: Sql Query For Getting Sundays And Saturdays in a given Year
Apr 23, 2012 06:19 AM|LINK
My Query Given Below
But i Got Some Error
yrb.yogi
Star
14460 Points
2402 Posts
Re: Sql Query For Getting Sundays And Saturdays in a given Year
Apr 23, 2012 07:30 AM|LINK
--you must use this query to insert records DECLARE @StartDate DATETIME SET @StartDate='01/01/2010' ;WITH CTE AS ( SELECT @StartDate as dt, 1 as Id UNION ALL SELECT DATEADD(dd,1,dt),CTE.Id + 1 FROM CTE WHERE Id <= 365 ) Insert Into HOLIDAY ( [HOLIDAY_DATE],[SPECIFICATION] ) --insert select query select dt,DATENAME(dw,dt) from CTE Where(DATEPART(dw,dt)=1 OR DATEPART(dw,dt)=7) OPTION (MAXRECURSION 0).Net All About