split date range to individual dates

Question

split date range to individual dates

hacker012

Valued Member

Points: 65
More actions
April 9, 2012 at 8:32 am

#271604

hi,all
i want split data like
id startdate enddate
1 2012-2-1 2012-2-3
to datas like:
id date
1 2012-2-1
2 2012-2-2
3 2012-2-3
who can help me ? Thx!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

You should be able to combine datediff, dateadd and a tally table to get what you are looking for. Read Jeff's article about what a tally table is and how to use it here. http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url%5D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286609 More actions · Answer 2

Something like this:

declare @StartDate datetime = '2/1/2012'

declare @EndDate datetime = '2/3/2012'

select ROW_NUMBER() over(order by t.N), dateadd(d, t.N - 1, @StartDate)

from Tally t

where N <= datediff(d, @StartDate, @EndDate) + 1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442470 More actions · Answer 3

Here is some more code to help you:

declare @TestTab table (

testid int identity(1,1),

startdate datetime,

enddate datetime);

insert into @TestTab(startdate, enddate)

select '20120201','20120203';

select * from @TestTab;

with e1 (N) as (

select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1),

e2 (N) as (select a.N from e1 a cross join e1 b), -- 100 rows

e4 (N) as (select a.N from e2 a cross join e2 b), -- 10000 rows

cteTally (N) as (

select row_number() over (order by (select null)) from e4)

select

dateadd(dd, (N - 1), startdate)

from

@TestTab

cross join cteTally

where

dateadd(dd, (N - 1), startdate) <= enddate

;

hacker012 Valued Member Points: 65 More actions · Answer 4

@SSCarpal Tunnel

Thanks very much, i think i've found solution!