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 |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2014-07-10 : 11:39:19
|
| Looking for assistance in returning multiple entries from a time span. I have a date, start-time, end-time and duration. I need the start-times separated in a list. It's fine if temp tables are needed - I have that clearance. (Don't worry about formatting the result - I really need the help on splitting into multiple rows.)Entry might look like:(datetime) date: 7/10/2014 (int) start-time 820(int) end-time 1000(int) duration 20The result would needed:(datetime) 7/10/2014 08:20:00(datetime) 7/10/2014 08:40:00(datetime) 7/10/2014 09:00:00(datetime) 7/10/2014 09:20:00(datetime) 7/10/2014 09:40:00 |
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2014-07-10 : 12:26:09
|
| Jingyang Li very graciously gave me an answer. Very elegant and works great - just in case someone else needs it:declare @date datetime = '2014-07-10 00:00:00.000' , @start int = 820 , @end int = 1000 , @duration int = 20 ; --minute declare @start2 varchar(5)=Left(Right('00'+Cast(@start as varchar(5)),4),2)+':'+Right(Cast(@start as varchar(5)),2) declare @end2 varchar(5)=Left(Right('00'+Cast(@end as varchar(5)),4),2)+':'+Right(Cast(@end as varchar(5)),2) ;with mycte as ( select *, dateadd(minute, timeslot*n,cast(convert(varchar(10),dt,101)+' '+ stime as datetime)) dt2 ,cast(convert(varchar(10),dt,101)+' '+ etime as datetime) dt_end from (Select @date as dt, @start2 stime, @end2 etime, @duration timeslot) t cross apply (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) d(n) ) select dt2 from mycte where dt2<dt_end |
 |
|
|
namman
Constraint Violating Yak Guru
285 Posts |
Posted - 2014-07-11 : 17:56:52
|
| Thanks |
|
|
|
|
|
|
|
|
Splitting time span into multiple rows