求sql

Question

有一个表，表中  2009-1-1  2009-1-2  2009-1-8   2009-1-15   ...断的，
我想要2009-1-1  2009-1-2   全的，如何做

Answer 1

select dateadd(dd,number,'2009-1-1')
from master..spt_values
where type='P' and dateadd(dd,number,'2009-1-1')<='2009-1-15'
/*
-----------------------
2009-01-01 00:00:00.000
2009-01-02 00:00:00.000
2009-01-03 00:00:00.000
2009-01-04 00:00:00.000
2009-01-05 00:00:00.000
2009-01-06 00:00:00.000
2009-01-07 00:00:00.000
2009-01-08 00:00:00.000
2009-01-09 00:00:00.000
2009-01-10 00:00:00.000
2009-01-11 00:00:00.000
2009-01-12 00:00:00.000
2009-01-13 00:00:00.000
2009-01-14 00:00:00.000
2009-01-15 00:00:00.000(15 個資料列受到影響)
*/

Answer 2

DECLARE @minDate datetime,@maxDate datetime;
SELECT @minDate=MIN(date),@maxDate=MAX(date) FROM tb;SELECT DATEADD(day,number,@minDate) AS date
FROM master.dbo.spt_values 
WHERE type = 'p'
    AND DATEADD(day,number,@minDate) <= @maxDate;

Answer 3

with data as
(
 select cast('2009-1-1' as datetime) as dt
 union all
 select data.dt+1 from data where dt<'2009-1-15'
)
select * from data

Answer 4

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-25 16:40:11.437●●●●●
　★★★★★soft_wsx★★★★★
*/  
if OBJECT_ID('tb') is not null drop table tb
  go
create table tb(id int,DateFrom  datetime,  DateTo datetime)
insert tb
select 
1,  '2009-01-01', GETDATE() select a.id,convert(nvarchar(10),dateadd(day,b.number,DateFrom),120) as 日期
  from tb a inner join master..spt_values b
  on b.number<=DATEDIFF(day,datefrom,dateto) and b.type='p' 
/*
id 日期
1 2009-01-01
1 2009-01-02
1 2009-01-03
1 2009-01-04
1 2009-01-05
1 2009-01-06
1 2009-01-07
1 2009-01-08
1 2009-01-09
1 2009-01-10
1 2009-01-11
1 2009-01-12
1 2009-01-13
1 2009-01-14
1 2009-01-15
1 2009-01-16
1 2009-01-17
1 2009-01-18
1 2009-01-19
*/像这样吗？

Answer 5

--1、得出一天的时间段记录。（如  1：00-2：00） (可以只用一个表)
select 时间段=right(100+number,2)+':00-'+right(101+number,2)+':00'
from master..spt_values 
where type='p'
    and number between 0 and 23 
    
/*
时间段
00:00-01:00
01:00-02:00
02:00-03:00
03:00-04:00
04:00-05:00
05:00-06:00
06:00-07:00
07:00-08:00
08:00-09:00
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00
13:00-14:00
14:00-15:00
15:00-16:00
16:00-17:00
17:00-18:00
18:00-19:00
19:00-20:00
20:00-21:00
21:00-22:00
22:00-23:00
23:00-24:00
*/

Answer 6

http://topic.csdn.net/u/20090906/17/a91cd31e-abf7-4190-8486-c5491aa63c71.html?seed=1380177511&r=59598572#r_59598572

Answer 7

declare @date datetime
set @date='2009-01-01'select [day]=ltrim(year(@date))+'-'+right(100+month(@date),2)+'-'+right('0'+ltrim(number),2)
from master..spt_values
where type='p'
  and number >=1 
  and number <= 15
/*day
----------------------
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10
2009-01-11
2009-01-12
2009-01-13
2009-01-14
2009-01-15(15 行受影响)*/