SQL out all Saturday Sunday date

SQL out all Saturday Sunday date

CREATE TABLE Satsun ([id] int identity (), [Date] datetime,[weekday] char (6))

Go

DECLARE @datetime datetime, @weekday char (6)

Set @datetime = ' 2007-1-1 '

While @datetime <= ' 2007-12-31 '

Begin

Select @weekday =datename (Weekday, @datetime)

If @weekday = ' Saturday ' Insert Satsun ([Date],[weekday]) VALUES (@datetime, @weekday)

If @weekday = ' Sunday ' Insert Satsun ([Date],[weekday]) VALUES (@datetime, ' Sunday ')

Select @datetime =dateadd (day,1, @datetime)

End

Go

SELECT * FROM Satsun

drop table Satsun

/*

ID Date Weekday

----------- ------------------------------------------------------ -------

1 2007-01-06 00:00:00.000 Saturday

2 2007-01-07 00:00:00.000 Sunday

3 2007-01-13 00:00:00.000 Saturday

4 2007-01-14 00:00:00.000 Sunday

5 2007-01-20 00:00:00.000 Saturday

6 2007-01-21 00:00:00.000 Sunday

7 2007-01-27 00:00:00.000 Saturday

8 2007-01-28 00:00:00.000 Sunday

9 2007-02-03 00:00:00.000 Saturday

......

100 2007-12-16 00:00:00.000 Sunday

101 2007-12-22 00:00:00.000 Saturday

102 2007-12-23 00:00:00.000 Sunday

103 2007-12-29 00:00:00.000 Saturday

104 2007-12-30 00:00:00.000 Sunday

*/(the number of rows affected is 104 rows)

****************************************************************************

DECLARE @t table (dd datetime)

DECLARE @d1 datetime

Select @d1 = ' 2007-1-1 '

while (year (@d1) <2008)

Begin

Insert INTO @t Select @d1

Set @d1 =dateadd (dd,1, @d1)

End

SELECT * FROM @t where DATEPART (WEEKDAY,DD) =6 or datepart (WEEKDAY,DD) =7

****************************************************************************

DECLARE @i int

DECLARE @d datetime

Set @i=1

Set @d= ' 2007-1-1 '

Create table #a (Dat datetime)

While year (@d) =2007

Begin

INSERT into #a values (@d)

Set @[email protected]+1

Set @d=dateadd (day,1,@d)

End

Select *,datepart (Dw,dat) as a from #a where DATEPART (dw,dat) =1 or datepart (dw,dat) =7

****************************************************************************

/*

Function: Calculates all dates for a certain number of weeks (such as Monday) in a certain time period

Design: ok_008

Time: 2006-10

*/

DECLARE @Date datetime

DECLARE @StartDate datetime

DECLARE @EndDate datetime

DECLARE @WeekDay int

DECLARE @i int

Set Datefirst 7--Setting the first day of the week

SET @StartDate = ' 2006-01-01 '--start date of the statistic

SET @EndDate = ' 2006-12-31 '--end date of the statistic

Set @WeekDay = 1-depending on the actual @ @DATEFIRST, the default is 7, such as @StartDate = ' 2006-01-01 ' when @WeekDay =3 represents Tuesday

SET @i=datepart (Weekday, @StartDate)

PRINT ' 1th Day of the week set @ @DATEFIRST: ' +cast (@ @DATEFIRST as nvarchar (1))

PRINT ' Start date corresponds to the day of the week: ' +cast (@i as nvarchar (1))

IF (@i<[email protected] and @i<7)

SET @[email protected]@i

ELSE IF (@i<[email protected] and @i=7)

SET @[email protected] @WeekDay

ELSE

SET @[email protected]@[email Protected][email protected]

SET @Date =dateadd (day,@i, @StartDate)

While @Date <[email protected]

BEGIN

IF (@StartDate <[email protected]) PRINT CONVERT (nvarchar (ten), @Date, 121)

SET @Date =dateadd (week,1, @Date)

END

GO

/* ============== Run Results ================*/

/*

1th Day of the week set @ @DATEFIRST: 7

The start date corresponds to the day of the week: 1

2006-01-01

2006-01-08

2006-01-15

2006-01-22

2006-01-29

..........

*/

Select DATEADD (Day,x,col), ' Tuesday ' from

(

Select cast (' 2006-1-1 ' as datetime) as Col

) a cross Join

(

SELECT Top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x

From (select 0 I UNION all select 1) b0

Cross joins (select 0 I UNION all select 2) b1

Cross joins (select 0 I UNION all select 4) b2

Cross joins (select 0 I UNION all select 8) B3

Cross joins (select 0 I UNION all Select) B4

Cross joins (select 0 I UNION all Select) B5

Cross joins (select 0 I UNION all Select) B6

Cross joins (select 0 i UNION all SELECT) b7

Cross joins (select 0 I UNION all select B8)

ORDER BY 1

) b

where DATEPART (Dw,dateadd (day,x,col)) =3 (this place changed value for other dates)

2006-01-03 00:00:00.000 Tuesday

2006-01-10 00:00:00.000 Tuesday

2006-01-17 00:00:00.000 Tuesday

2006-01-24 00:00:00.000 Tuesday

2006-01-31 00:00:00.000 Tuesday

****************************************************************************

DECLARE @t TABLE (date0 DATETIME)

DECLARE @st datetime, @et datetime

SET @st = ' 2007-01-01 '

SET @et = ' 2008-01-01 '

While @st < @et

BEGIN

INSERT into @t VALUES (@st)

SELECT @st =dateadd (day,1, @st)

END

SELECT Date0,datename (weekday,date0) from @t WHERE DATEPART (weekday,[email protected] @DATEFIRST-1) in (6,7)

****************************************************************************

Due to work requirements, a function that calculates the difference between two dates is written under SQL Server 2005. function is calculated in one weeks 5 days, not excluding 51 national day and other holidays. The code is as follows:

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

--Author:sinmen

--Create date:2007-11-01

--Description: Calculates the difference between two dates in a weekday

-- =============================================

ALTER FUNCTION [dbo]. [Workdatediff]

(

@begin_date datetime,

@end_date datetime

)

RETURNS int

As

BEGIN

DECLARE @return_date_quantity int

DECLARE @temp datetime

DECLARE @week_quantity int

DECLARE @day_quantity int

DECLARE @begin_day_of_week int

DECLARE @end_day_of_week int

DECLARE @add_begin_day_quantity int

DECLARE @add_end_day_quantity int

Set @day_quantity = Datediff (d, @begin_date, @end_date)

--Determine if the incoming start date is greater than the end date

If @day_quantity < 0

Begin

Set @temp = @begin_date

Set @begin_date = @end_date

Set @end_date = @temp

End

Set @week_quantity = Abs (Datediff (WW, @begin_date, @end_date))-1

If @week_quantity < 0

Set @week_quantity = 0

Set @begin_day_of_week = Datepart (DW, @begin_date)-1

Set @end_day_of_week = Datepart (DW, @end_date)-1

Set @add_begin_day_quantity = case

When @begin_day_of_week > 5 then 0

Else 6-@begin_day_of_week-(@begin_day_of_week + 1)

End

Set @add_end_day_quantity = case

When ABS (@day_quantity) < 8 then 0

When @end_day_of_week > 5 then 5

else @end_day_of_week

End

If @day_quantity = 0

Set @return_date_quantity = 0

Else

Set @return_date_quantity = @week_quantity * 5 + @add_begin_day_quantity + @add_end_day_quantity

If @day_quantity < 0

Set @return_date_quantity = @return_date_quantity *-1

RETURN @return_date_quantity

END

GO

SET Ansi_nulls OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

SQL out all Saturday Sunday date