如果你的日期列为colselect col from [tablename] where year(col)='2007'--07年 and datepart(wk,col)=1 --第一周 and datepart(dw,col)=2 --星期1
and datepart(dw,col)=2 --星期1 因为对于dw部分, sunday=1,monday=2 提供给你一个生成指定起始日期和结尾日期时间结构表的sql,你可以用它生成环境来测试一下。if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[时间]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[时间] GOCREATE TABLE [dbo].[时间] ( [time_id] [int] IDENTITY (1, 1) NOT NULL , [the_date] [datetime] NULL , [the_day] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL , [the_month] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL , [the_year] [smallint] NULL , [day_of_month] [smallint] NULL , [week_of_year] [float] NULL , [month_of_year] [smallint] NULL , [quarter] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL , [tenday_of_month] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL , [half_year] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL , ) ON [PRIMARY] GOtruncate table 时间 go declare @DateJ int declare @CurDate datetime declare @StartDate datetime declare @EndDate datetime set @StartDate='2007-01-01' ---------这里填写起始时间 set @EndDate= '2008-12-31' ---------这里填写结束时间 --SELECT DATEDIFF(day, @StartDate, @EndDate) into @DateJ
set @CurDate= @StartDate while @CurDate<=@EndDate BEGIN insert into 时间 (the_date,the_day,the_month,the_year,day_of_month, week_of_year,month_of_year,quarter,tenday_of_month,half_year) values (@CurDate, case when DATEPART(weekday, @CurDate)=1 then '星期日' when DATEPART(weekday, @CurDate)=2 then '星期一' when DATEPART(weekday, @CurDate)=3 then '星期二' when DATEPART(weekday, @CurDate)=4 then '星期三' when DATEPART(weekday, @CurDate)=5 then '星期四' when DATEPART(weekday, @CurDate)=6 then '星期五' when DATEPART(weekday, @CurDate)=7 then '星期六' end , case when DATEPART(Month, @CurDate)=1 then '一月' when DATEPART(Month, @CurDate)=2 then '二月' when DATEPART(Month, @CurDate)=3 then '三月' when DATEPART(Month, @CurDate)=4 then '四月' when DATEPART(Month, @CurDate)=5 then '五月' when DATEPART(Month, @CurDate)=6 then '六月' when DATEPART(Month, @CurDate)=7 then '七月' when DATEPART(Month, @CurDate)=8 then '八月' when DATEPART(Month, @CurDate)=9 then '九月' when DATEPART(Month, @CurDate)=10 then '十月' when DATEPART(Month, @CurDate)=11 then '十一月' when DATEPART(Month, @CurDate)=12 then '十二月' end , DATENAME(year, @CurDate) , DATENAME(day, @CurDate) , DATENAME(week, @CurDate),DATENAME(month, @CurDate) ,DATENAME(quarter, @CurDate) , case when DATEPART(day, @CurDate)<=10 then '上旬' when DATEPART(day, @CurDate)<=20 and DATEPART(day, @CurDate)>10 then '中旬' when DATEPART(day, @CurDate)>20 then '下旬' end, case when DATEPART(month, @CurDate)<=6 then '上半年' when DATEPART(month, @CurDate)>6 then '下半年' end ) --print @CurDate set @CurDate=DATEADD(day,1,@CurDate) end查询: select the_date from [时间] where year(the_date)='2007' and datepart(wk,the_date)=1 and datepart(dw,the_date)=2结果; ------------ the_date 2007-01-01 00:00:00.000
declare @year char(4) declare @week int set @year='2007' set @week=1 select dateadd(wk,@week-1,@year)-datepart(weekday,dateadd(wk,@week-1,@year))+2--结果
where year(col)='2007'--07年
and datepart(wk,col)=1 --第一周
and datepart(dw,col)=2 --星期1
因为对于dw部分,
sunday=1,monday=2
提供给你一个生成指定起始日期和结尾日期时间结构表的sql,你可以用它生成环境来测试一下。if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[时间]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[时间]
GOCREATE TABLE [dbo].[时间] (
[time_id] [int] IDENTITY (1, 1) NOT NULL ,
[the_date] [datetime] NULL ,
[the_day] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[the_month] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[the_year] [smallint] NULL ,
[day_of_month] [smallint] NULL ,
[week_of_year] [float] NULL ,
[month_of_year] [smallint] NULL ,
[quarter] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[tenday_of_month] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[half_year] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
GOtruncate table 时间
go
declare @DateJ int
declare @CurDate datetime
declare @StartDate datetime
declare @EndDate datetime
set @StartDate='2007-01-01' ---------这里填写起始时间
set @EndDate= '2008-12-31' ---------这里填写结束时间
--SELECT DATEDIFF(day, @StartDate, @EndDate) into @DateJ
set @CurDate= @StartDate
while @CurDate<=@EndDate BEGIN
insert into 时间 (the_date,the_day,the_month,the_year,day_of_month,
week_of_year,month_of_year,quarter,tenday_of_month,half_year)
values (@CurDate,
case when DATEPART(weekday, @CurDate)=1 then '星期日'
when DATEPART(weekday, @CurDate)=2 then '星期一'
when DATEPART(weekday, @CurDate)=3 then '星期二'
when DATEPART(weekday, @CurDate)=4 then '星期三'
when DATEPART(weekday, @CurDate)=5 then '星期四'
when DATEPART(weekday, @CurDate)=6 then '星期五'
when DATEPART(weekday, @CurDate)=7 then '星期六'
end ,
case when DATEPART(Month, @CurDate)=1 then '一月'
when DATEPART(Month, @CurDate)=2 then '二月'
when DATEPART(Month, @CurDate)=3 then '三月'
when DATEPART(Month, @CurDate)=4 then '四月'
when DATEPART(Month, @CurDate)=5 then '五月'
when DATEPART(Month, @CurDate)=6 then '六月'
when DATEPART(Month, @CurDate)=7 then '七月'
when DATEPART(Month, @CurDate)=8 then '八月'
when DATEPART(Month, @CurDate)=9 then '九月'
when DATEPART(Month, @CurDate)=10 then '十月'
when DATEPART(Month, @CurDate)=11 then '十一月'
when DATEPART(Month, @CurDate)=12 then '十二月'
end ,
DATENAME(year, @CurDate) ,
DATENAME(day, @CurDate) ,
DATENAME(week, @CurDate),DATENAME(month, @CurDate) ,DATENAME(quarter, @CurDate)
,
case when DATEPART(day, @CurDate)<=10 then '上旬'
when DATEPART(day, @CurDate)<=20 and DATEPART(day, @CurDate)>10 then '中旬'
when DATEPART(day, @CurDate)>20 then '下旬'
end,
case when DATEPART(month, @CurDate)<=6 then '上半年'
when DATEPART(month, @CurDate)>6 then '下半年'
end
)
--print @CurDate
set @CurDate=DATEADD(day,1,@CurDate)
end查询:
select the_date from [时间]
where year(the_date)='2007'
and datepart(wk,the_date)=1
and datepart(dw,the_date)=2结果;
------------
the_date
2007-01-01 00:00:00.000
declare @week int
set @year='2007'
set @week=1
select dateadd(wk,@week-1,@year)-datepart(weekday,dateadd(wk,@week-1,@year))+2--结果
2007-01-01 00:00:00.000(所影响的行数为 1 行)