hi
i want to write some sort of query which will return all saturday and sunday of year 2007 with date...
thanx a lot
here you go..
Code Snippet
Declare @.Year as Int;
Declare @.Weekends Table(Date DateTime);
Declare @.Date as DateTime;
Declare @.EndDate as DateTime;
Select @.Year = 2000;
Select @.Date = Cast(Cast(@.Year as Varchar) + '-01-01' as Datetime) ,
@.EndDate = Cast(Cast(@.Year as Varchar) + '-12-31' as Datetime);
Select @.Date = DateAdd(DD,7-Datepart(w,@.Date),@.Date);
While @.Date <= @.EndDate
Begin
Insert Into @.Weekends
Select @.Date
Union All
Select DateAdd(DD,1,@.Date);
Select @.Date = DateAdd(DD,7,@.Date);
End
Select * From @.Weekends
|||Hi,
I hope the below query may help you
declare @.date as datetime
select @.date = dateadd(dd, -1 * datepart(dy, getdate()) + 1, getdate())
while year(@.date)=2007
begin
if DATEPART(dw, @.date) in (1,7)
print cast(@.date as varchar(10))
set @.date = dateadd(dd,1,@.date)
end
Eralper
http://www.kodyaz.com
|||
hey mani thanx for reply...
i want date with day...i mean the corresponding date is saturday or sunday .......how we can do?
|||Here it is with day..
Code Snippet
Declare @.Year as Int;
Declare @.Weekends Table(Date DateTime,Day varchar(10));
Declare @.Date as DateTime;
Declare @.EndDate as DateTime;
Select @.Year = 2006;
Select @.Date = Cast(Cast(@.Year as Varchar) + '-01-01' as Datetime) ,
@.EndDate = Cast(Cast(@.Year as Varchar) + '-12-31' as Datetime);
Select @.Date = DateAdd(DD,1-Datepart(w,@.Date),@.Date);
While @.Date <= @.EndDate
Begin
Insert Into @.Weekends
Select DateAdd(DD,-1,@.Date), Datename(dw,DateAdd(DD,-1,@.Date)) Where Year(DateAdd(DD,-1,@.Date)) = @.Year
Union
Select @.Date, Datename(dw,@.Date) Where Year(@.Date) = @.Year
Select @.Date = DateAdd(DD,7,@.Date);
End
Select * From @.Weekends
|||yes mani this is my need thanx a lot....but here i m getting one entry of 2006-12-31 00:00:00.000......
Select @.Year = 2007;
here i set @.year 2007 but i m getting one entry of 2006...i don't want that entry....
sorry for trouble...
|||declare @.date as datetime
declare @.year as int
set @.year=2007
Declare @.Weekends Table(Date varchar(12),[Day] varchar(10));
select @.date = dateadd(dd, -1 * datepart(dy, getdate()) + 1, getdate())
while year(@.date)=@.year
begin
if DATEPART(dw, @.date) in (1,7)
begin
Insert Into @.Weekends
select cast(@.date as varchar(12)),cast(DATENAME(dw,@.Date) as varchar(10))
end
set @.date = dateadd(dd,1,@.date)
end
Select * From @.Weekends
