holiday's

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

|||opps yes.. Fixed it..|||thanx a lot|||welcome.. |||

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