java获取今年所有周六周日_SQL取出 所有周六 周日的日期

行)

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

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@iintdeclare@ddatetimeset@i=1set@d='2007-1-1'Createtable#a (Datdatetime)whileyear(@d)=2007begininsertinto#avalues(@d)set@i=@i+1set@d=dateadd(day,1,@d)endselect*,datepart(dw,Dat)asafrom#awheredatepart(dw,Dat)=1ordatepart(dw,Dat)=7

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

/*功能: 计算在某一段时间内某周几(如星期一)的所有日期

设计：OK_008

时间：2006-10*/DECLARE@DatedatetimeDECLARE@StartDatedatetimeDECLARE@EndDatedatetimeDECLARE@WeekDayintDECLARE@iintSETDATEFIRST7--设置每周的第一天SET@StartDate='2006-01-01'--统计的开始日期SET@EndDate='2006-12-31'--统计的结束日期SET@WeekDay=1--根据实际的@@DATEFIRST而定，一般默认是7，如 @StartDate='2006-01-01'时候， @WeekDay=3表示星期二SET@i=DATEPART(weekday,@StartDate)PRINT'每周的第1天设置@@DATEFIRST:'+CAST(@@DATEFIRSTASnvarchar(1))PRINT'开始日期对应一周的第几天:'+CAST(@iASnvarchar(1))IF(@i<=@WeekDayAND@i<7)SET@i=@WeekDay-@iELSEIF(@i<=@WeekDayAND@i=7)SET@i=@i-@WeekDayELSESET@i=@@DATEFIRST-@i+@WeekDaySET@Date=DATEADD(day,@i,@StartDate)WHILE@Date<=@EndDateBEGINIF(@StartDate<=@Date)PRINTCONVERT(nvarchar(10),@Date,121)SET@Date=DATEADD(Week,1,@Date)ENDGO/*==============运行结果================*//*每周的第1天设置@@DATEFIRST: 7

开始日期对应一周的第几天: 1

2006-01-01

2006-01-08

2006-01-15

2006-01-22

2006-01-29

..........*/selectdateadd(day,x,col),'星期二'from(selectcast('2006-1-1'asdatetime)ascol

)acrossjoin(SELECTtop365b8.i+b7.i+b6.i+b5.i+b4.i+b3.i+b2.i+b1.i+b0.i xFROM(SELECT0iUNIONALLSELECT1) b0CROSSJOIN(SELECT0iUNIONALLSELECT2) b1CROSSJOIN(SELECT0iUNIONALLSELECT4) b2CROSSJOIN(SELECT0iUNIONALLSELECT8) b3CROSSJOIN(SELECT0iUNIONALLSELECT16) b4CROSSJOIN(SELECT0iUNIONALLSELECT32) b5CROSSJOIN(SELECT0iUNIONALLSELECT64) b6CROSSJOIN(SELECT0iUNIONALLSELECT128) b7CROSSJOIN(SELECT0iUNIONALLSELECT256) b8orderby1)bwheredatepart(dw,dateadd(day,x,col))=3(这个地方改值换取其他日期)2006-01-0300:00:00.000星期二2006-01-1000:00:00.000星期二2006-01-1700:00:00.000星期二2006-01-2400:00:00.000星期二2006-01-3100:00:00.000星期二

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

DECLARE @t TABLE(date0 DATETIME)

DECLARE @st DATETIME,@et DATETIME

SET @st='2007-01-01'

SET @et='2008-01-01'

WHILE @st

BEGIN

INSERT INTO @t VALUES(@st)

SELECT @st=DATEADD(DAY,1,@st)

END

SELECT date0,DATENAME(weekday,date0) FROM @t WHERE DATEPART(weekday,date0+@@DATEFIRST-1) IN (6,7)

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

由于工作需要，在SQL Server 2005 下面写了一个计算两个日期之间相差工作日的函数。函数是以一个星期5天工作日计算，没有剔除五一国庆等假期。代码如下：

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

-- Author:  Sinmen

-- Create date: 2007-11-01

-- Description: 计算两个日期之间相差的工作日

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

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)

--判断传入的开始日期是否比结束日期大

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 --(5 - @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

Original text:http://www.cnblogs.com/wayne-ivan/archive/2008/04/10/1146308.html