1

There are many questions similar to this one however I have been unable to locate one which can meet my requirements. So apologies if I have missed one....

I have a StartDate and EndDate like so:

Id            StartDate                            EndDate                  
1   2015-02-05 09:00:00.0000000 +01:00  2015-02-09 17:30:00.0000000 +01:00

Now from this I am trying to list the dates and times (Including the StartDate and EndDate) between these 2 values like so:

Id            StartDate                            EndDate                  
1   2015-02-05 09:00:00.0000000 +01:00  2015-02-05 17:30:00.0000000 +01:00  --does not include this date in the query below  
1   2015-02-06 09:00:00.0000000 +01:01  2015-02-06 17:30:00.0000000 +01:00    
1   2015-02-07 09:00:00.0000000 +01:01  2015-02-07 17:30:00.0000000 +01:00    
1   2015-02-08 09:00:00.0000000 +01:02  2015-02-08 17:30:00.0000000 +01:00    
1   2015-02-09 09:00:00.0000000 +01:02  2015-02-09 17:30:00.0000000 +01:00

There will be multiple Id's in this table so grouping them by this would be extremely useful also.

Can this be done using this Table Structure?

CREATE TABLE TestDateRange (Id int identity (1,1) not null,
StartDate DATETIMEOFFSET,
EndDate DATETIMEOFFSET
UserID int)

ALTER TABLE TestDateRange ADD CONSTRAINT Id_PK PRIMARY KEY (Id)

Looking at the similar post mentioned, this code does not achieve my desired affect: (misses the startDate off which I am working on now)

SELECT
    v.Id,
    d.CalendarDate
FROM
    Dates d INNER JOIN
    TestDateRange v ON
        d.CalendarDate >= v.StartDate AND
        d.CalendarDate <= v.EndDate

group by
    d.CalendarDate,
    v.Id

SOLVED thanks to Bulat for the help:

 d.CalendarDate >= CAST(v.StartDate AS DATE)  AND
Crezzer7
  • 2,265
  • 6
  • 32
  • 63
  • possible duplicate of [Convert Date Range to Individual Days](http://stackoverflow.com/questions/18151733/convert-date-range-to-individual-days) – Bulat Aug 27 '15 at 13:13
  • looking at Valex's answer on the potential duplicate, This may solve my issue however I cannot get this to work... – Crezzer7 Aug 27 '15 at 13:15
  • Once you find a good way to generate Date dimension table that covers all dates in your domain, it will be easy to proceed with the query. – Bulat Aug 27 '15 at 13:23
  • well in that link one user suggests making an additional table full of dates... but that seems unnecessary to me... this will involve inserting thousands of extra rows of data in the long run. In 20 years your looking at around 7300 rows – Crezzer7 Aug 27 '15 at 13:25
  • 7300 is not much, it is close to nothing on any scale. You can calculate this table on the fly each time, but having date table in the database is handy, especially if you are doing ad-hoc analysis or reporting. – Bulat Aug 27 '15 at 13:27
  • I have attempted this code and it does not return the right values, added to question – Crezzer7 Aug 27 '15 at 13:30
  • you need to round your start date – Bulat Aug 27 '15 at 13:51

0 Answers0