guddu567568


hi

i want to write some sort of query which will return all saturday and sunday of year 2007 with date...

thanx a lot




Re: holiday's

Manivannan.D.Sekaran


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







Re: holiday's

eralper

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







Re: holiday's

guddu

hey mani thanx for reply...

i want date with day...i mean the corresponding date is saturday or sunday .......how we can do





Re: holiday's

Manivannan.D.Sekaran

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






Re: holiday's

guddu

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...






Re: holiday's

Manivannan.D.Sekaran

opps yes.. Fixed it..




Re: holiday's

guddu

thanx a lot



Re: holiday's

Manivannan.D.Sekaran

welcome..




Re: holiday's

vinuraj

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