T-SQL-master..spt_values應用

在系統中查詢 master..spt_values 這個表並僅顯示number欄位，
會回傳 0～2047的流水編號，當需要產生一些流水編的數值時，
可利用此回傳特性，應用到我們需要的特定資料格式。

select number
from master..spt_values
where type='p'

number
-----------
0
1
2
...

(2048 個資料列受到影響)

--產生期別
select '2011'+REPLICATE(0,2-LEN(cast(number as varchar(2))))+cast(number as varchar(2))
from master..spt_values
where type='p'
and number between 1 and 12

-------
201101
201102
201103
201104
201105
201106
201107
201108
201109
201110
201111
201112

(12 個資料列受到影響)

--產生日期區間-1
select convert(varchar(10),dateadd(dd,number+1,'2011-01-01'),120)
from master..spt_values
where type='p' and number < datediff(dd,'2011-01-01','2011-01-10')-1

----------
2011-01-02
2011-01-03
2011-01-04
2011-01-05
2011-01-06
2011-01-07
2011-01-08
2011-01-09

(8 個資料列受到影響)

--產生日期區間-2
select convert(varchar(10),dateadd(dd,number+1,'2010-12-31'),120)
from master..spt_values
where type='p' and number < 31

----------
2011-01-01
2011-01-02
2011-01-03
2011-01-04
2011-01-05
2011-01-06
2011-01-07
2011-01-08
2011-01-09
2011-01-10
2011-01-11
2011-01-12
2011-01-13
2011-01-14
2011-01-15
2011-01-16
2011-01-17
2011-01-18
2011-01-19
2011-01-20
2011-01-21
2011-01-22
2011-01-23
2011-01-24
2011-01-25
2011-01-26
2011-01-27
2011-01-28
2011-01-29
2011-01-30
2011-01-31

(31 個資料列受到影響)

參考自：巧用master..spt_values表輸出數字或者時間常量表、CSDN