Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Gaillimh
Starting Member
3 Posts |
 Posted - 2011-05-17 : 09:30:48
|
| Hi,I have a start date and an end date:$start_date = '2011-05-01'$end_date = '2011-05-04'How can I get SQL insert four records between those dates so that my table looks like this:id | date --------------- 1 | 2011-05-01 2 | 2011-05-02 3 | 2011-05-034 | 2011-05-04Any tips greatly appreciated. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-05-17 : 09:42:52
|
| [code]DECLARE @start_date DATETIME; SET @start_date = '2011-05-01';declare @end_date DATETIME; SET @end_date = '2011-05-04';DECLARE @days INT SET @days = DATEDIFF(dd, @start_date,@end_date)+1;;WITH N(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < @days)SELECT n,DATEADD(dd,n,@start_date)FROM N[/code] |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-05-17 : 09:44:04
|
| SELECT DISTINCt DATEADD(day, number,date)FROM master..spt_valuesCROSS JOIN @tablewhere [type] = 'p' and DATEADD(day, number,date) <= (select max(date) from @table)JimEveryday I learn something that somebody else already knew |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-05-17 : 09:52:54
|
use F_TABLE_DATE
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
Insert multiple rows between dates