Tag Info

New answers tagged

1

Assuming your largest range will be 366 days (and you can adjust that number), I think this approach will scale better than a recursive CTE or a while loop. This only handles days, because it's not quite clear to me from the question what the output should look like, exactly, when the range is months or years. ;WITH x AS ( SELECT TOP (366) n = number ...


1

OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table. In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code. This version honours the time component and will scale well. The calendar table is one I use in my dev. Please note this is intended as a ...


0

This should do what you need. DECLARE @Interval NVARCHAR(10), @I DATETIME, @IntervalMin DATETIME, @IntervalMax DATETIME --- Set Interval DAY or MONTH or YEAR ----------------- SET @Interval = 'DAY' ------------------------------------------------------- DECLARE @Records TABLE (StartDateTime DATETIME ,EndDateTime DATETIME) INSERT INTO @Records ...



Top 50 recent answers are included