34,590
社区成员
发帖
与我相关
我的任务
分享
select
*
from master..spt_values
where type='P' and dateadd(dd,number,'2011-01-01')<='2011-12-31'
你查查这能查出来多少行?select
dateadd(dd,number,'2011-01-01') as the_date,
datename(weekday,dateadd(dd,number,'2011-01-01')) as the_day,
month(dateadd(dd,number,'2011-01-01')) as the_month,
year(dateadd(dd,number,'2011-01-01')) as the_year,
datepart(dd,dateadd(dd,number,'2011-01-01')) as day_of_month,
datepart(wk,dateadd(dd,number,'2011-01-01')) as week_of_year
from (select top 1000 number=row_number() over(order by getdate())-1 from sys.objects,sys.columns) t
where dateadd(dd,number,'2011-01-01')<='2011-12-31'
DECLARE @RQ DATETIME
SET @RQ='2010-01-01'
SELECT DATEADD(DD,number,@RQ) as the_date
,DATENAME(WEEKDAY,DATEADD(DD,number,@RQ)) as the_day
,MONTH(DATEADD(DD,number,@RQ)) as the_month
,YEAR(DATEADD(DD,number,@RQ)) as the_year
,DAY(DATEADD(DD,number,@RQ)) as day_of_month
,DATEPART(WEEK,DATEADD(DD,number,@RQ)) as week_of_year
,12 as month_of_year
FROM master..spt_values WHERE TYPE='P' AND number<365
DECLARE @RQ DATETIME
SET @RQ='2010-01-01'
SELECT DATEADD(DD,number,@RQ)
FROM master..spt_values WHERE TYPE='P' AND number<365
select
dateadd(dd,number,'2011-01-01') as the_date,
datename(weekday,dateadd(dd,number,'2011-01-01')) as the_day,
month(dateadd(dd,number,'2011-01-01')) as the_month,
year(dateadd(dd,number,'2011-01-01')) as the_year,
datepart(dd,dateadd(dd,number,'2011-01-01')) as day_of_month,
datepart(wk,dateadd(dd,number,'2011-01-01')) as week_of_year
from master..spt_values
where type='P' and dateadd(dd,number,'2011-01-01')<='2011-12-31'
/**
the_date the_day the_month the_year day_of_month week_of_year
----------------------- ------------------------------ ----------- ----------- ------------ ------------
2011-01-01 00:00:00.000 星期六 1 2011 1 1
2011-01-02 00:00:00.000 星期日 1 2011 2 2
2011-01-03 00:00:00.000 星期一 1 2011 3 2
2011-01-04 00:00:00.000 星期二 1 2011 4 2
2011-01-05 00:00:00.000 星期三 1 2011 5 2
2011-01-06 00:00:00.000 星期四 1 2011 6 2
2011-01-07 00:00:00.000 星期五 1 2011 7 2
2011-01-08 00:00:00.000 星期六 1 2011 8 2
2011-01-09 00:00:00.000 星期日 1 2011 9 3
2011-01-10 00:00:00.000 星期一 1 2011 10 3
2011-01-11 00:00:00.000 星期二 1 2011 11 3
2011-01-12 00:00:00.000 星期三 1 2011 12 3
2011-01-13 00:00:00.000 星期四 1 2011 13 3
2011-01-14 00:00:00.000 星期五 1 2011 14 3
2011-01-15 00:00:00.000 星期六 1 2011 15 3
2011-01-16 00:00:00.000 星期日 1 2011 16 4
2011-01-17 00:00:00.000 星期一 1 2011 17 4
2011-01-18 00:00:00.000 星期二 1 2011 18 4
2011-01-19 00:00:00.000 星期三 1 2011 19 4
2011-01-20 00:00:00.000 星期四 1 2011 20 4
2011-01-21 00:00:00.000 星期五 1 2011 21 4
2011-01-22 00:00:00.000 星期六 1 2011 22 4
2011-01-23 00:00:00.000 星期日 1 2011 23 5
2011-01-24 00:00:00.000 星期一 1 2011 24 5
2011-01-25 00:00:00.000 星期二 1 2011 25 5
2011-01-26 00:00:00.000 星期三 1 2011 26 5
2011-01-27 00:00:00.000 星期四 1 2011 27 5
2011-01-28 00:00:00.000 星期五 1 2011 28 5
2011-01-29 00:00:00.000 星期六 1 2011 29 5
2011-01-30 00:00:00.000 星期日 1 2011 30 6
2011-01-31 00:00:00.000 星期一 1 2011 31 6
2011-02-01 00:00:00.000 星期二 2 2011 1 6
2011-02-02 00:00:00.000 星期三 2 2011 2 6
2011-02-03 00:00:00.000 星期四 2 2011 3 6
2011-02-04 00:00:00.000 星期五 2 2011 4 6
2011-02-05 00:00:00.000 星期六 2 2011 5 6
2011-02-06 00:00:00.000 星期日 2 2011 6 7
2011-02-07 00:00:00.000 星期一 2 2011 7 7
2011-02-08 00:00:00.000 星期二 2 2011 8 7
2011-02-09 00:00:00.000 星期三 2 2011 9 7
2011-02-10 00:00:00.000 星期四 2 2011 10 7
2011-02-11 00:00:00.000 星期五 2 2011 11 7
2011-02-12 00:00:00.000 星期六 2 2011 12 7
2011-02-13 00:00:00.000 星期日 2 2011 13 8
2011-02-14 00:00:00.000 星期一 2 2011 14 8
2011-02-15 00:00:00.000 星期二 2 2011 15 8
2011-02-16 00:00:00.000 星期三 2 2011 16 8
2011-02-17 00:00:00.000 星期四 2 2011 17 8
2011-02-18 00:00:00.000 星期五 2 2011 18 8
2011-02-19 00:00:00.000 星期六 2 2011 19 8
2011-02-20 00:00:00.000 星期日 2 2011 20 9
2011-02-21 00:00:00.000 星期一 2 2011 21 9
2011-02-22 00:00:00.000 星期二 2 2011 22 9
2011-02-23 00:00:00.000 星期三 2 2011 23 9
2011-02-24 00:00:00.000 星期四 2 2011 24 9
2011-02-25 00:00:00.000 星期五 2 2011 25 9
2011-02-26 00:00:00.000 星期六 2 2011 26 9
2011-02-27 00:00:00.000 星期日 2 2011 27 10
2011-02-28 00:00:00.000 星期一 2 2011 28 10
2011-03-01 00:00:00.000 星期二 3 2011 1 10
2011-03-02 00:00:00.000 星期三 3 2011 2 10
2011-03-03 00:00:00.000 星期四 3 2011 3 10
2011-03-04 00:00:00.000 星期五 3 2011 4 10
2011-03-05 00:00:00.000 星期六 3 2011 5 10
2011-03-06 00:00:00.000 星期日 3 2011 6 11
2011-03-07 00:00:00.000 星期一 3 2011 7 11
2011-03-08 00:00:00.000 星期二 3 2011 8 11
2011-03-09 00:00:00.000 星期三 3 2011 9 11
2011-03-10 00:00:00.000 星期四 3 2011 10 11
......
(365 行受影响)
**/
DECLARE @RQ DATETIME,@I INT
DECLARE @RESULT TABLE (RQ DATETIME)
SET @RQ='2010-01-01'
SET @I=0
WHILE @I<365
BEGIN
INSERT INTO @RESULT
SELECT DATEADD(DD,@I,@RQ)
SET @I=@I+1
END
SELECT * FROM @RESULT