master.dbo.spt

--统计某月份周情况 没有的补0if object_id('temp_tb') is not null drop table temp_tb

create table temp_tb([ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,[test_values] int NULL,[time] datetime null,);go

insert into temp_tb([test_values],[time]) select 3,'2009-2-1 10:12:30' union allselect 5,'2009-2-5 09:20:23' union allselect 6,'2009-2-6 11:21:34' union allselect 7,'2009-2-8 12:22:12' union allselect 2,'2009-2-10 16:45:25' union allselect 3,'2009-2-13 13:21:14' union allselect 5,'2009-2-15 15:58:09' union allselect 1,'2009-2-15 08:35:47' union allselect 1,'2009-2-15 09:13:07' union allselect 9,'2009-2-15 09:15:04'

select * from temp_tb

SELECT    A.[Week],    ISNULL(COUNT(B.test_values),0) AS numFROM (    SELECT        datepart(week,dateadd(week,0,dateadd(day,number,'2009-02-01'))) AS [Week]    FROM master.dbo.spt_values     WHERE type='p' AND number BETWEEN 0 AND 27    GROUP BY datepart(week,dateadd(week,0,dateadd(day,number,'2009-02-01')))) AS ALEFT JOIN temp_tb AS B    ON A.[Week]=DATEPART(week,time)GROUP BY A.[Week]

 

SELECT DATEPART(WEEK,'2009-10-30 12:15:32.1234567 +05:10')  --44周

/*ID                                      test_values time--------------------------------------- ----------- -----------------------1                                       3           2009-02-01 10:12:30.0002                                       5           2009-02-05 09:20:23.0003                                       6           2009-02-06 11:21:34.0004                                       7           2009-02-08 12:22:12.0005                                       2           2009-02-10 16:45:25.0006                                       3           2009-02-13 13:21:14.0007                                       5           2009-02-15 15:58:09.0008                                       1           2009-02-15 08:35:47.0009                                       1           2009-02-15 09:13:07.00010                                      9           2009-02-15 09:15:04.000

(10 行受影响)

Week        num----------- -----------6           37           38           49           0

*/

 

--1、得出一天的时间段记录。select 时间段=ltrim(a.number)+':00-'+ltrim(b.number)+':00'from master..spt_values a,master..spt_values bwhere a.type='p'and b.type='p' and a.number between 1 and 24  and b.number between 1 and 24  and a.number=b.number-1

/*时间段-------------------------------1:00-2:002:00-3:003:00-4:004:00-5:005:00-6:006:00-7:007:00-8:008:00-9:009:00-10:0010:00-11:0011:00-12:0012:00-13:0013:00-14:0014:00-15:0015:00-16:0016:00-17:0017:00-18:0018:00-19:0019:00-20:0020:00-21:0021:00-22:0022:00-23:0023:00-24:00

(23 行受影响)*/

 

--2、通过1个select语句某个月的所有日期记录declare @date datetimeset @date='2009-11-23'

select [day]=ltrim(year(@date))+right(100+month(@date),2)+right('0'+ltrim(number),2)from master..spt_valueswhere type='p'  and number >=1   and number <= datediff(dd,@date,dateadd(month,1,@date))

/*day--------------------200911012009110220091103200911042009110520091106200911072009110820091109200911102009111120091112200911132009111420091115200911162009111720091118200911192009112020091121200911222009112320091124200911252009112620091127200911282009112920091130

(30 行受影响)*/

本文来自博客，转载请标明出处：http://blog.csdn.net/fisea/archive/2010/05/13/5587464.aspx