Menu
I would like list dates between two date in a SQL Server stored procedure.
For example:
Date1: 2015-05-28 Date2: 2015-05-31 Results :
2015-05-29 2015-05-30 How to calculate all dates between two given dates?
Regards,
446
We can get the dates between two dates with single method call using the dedicated datesUntil method of a LocalDate class. The datesUntill returns the sequentially ordered Stream of dates starting from the date object whose method is called to the date given as method argument.
DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106'; SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a. object_id) - 1, @MinDate) FROM sys. all_objects a CROSS JOIN sys.
Since Excel dates are whole numbers, all you need to do is ask Sequence() to insert an array from a Start Date to an End Date. Sequence(1 + EndDate – StartDate, 1, StartDate) the formula will spill the dates into the cells below.
You can use a numbers table:
DECLARE @Date1 DATE, @Date2 DATE SET @Date1 = '20150528' SET @Date2 = '20150531' SELECT DATEADD(DAY,number+1,@Date1) [Date] FROM master..spt_values WHERE type = 'P' AND DATEADD(DAY,number+1,@Date1) < @Date2 Results:
╔════════════╗ ║ Date ║ ╠════════════╣ ║ 2015-05-29 ║ ║ 2015-05-30 ║ ╚════════════╝ Use this,
DECLARE @start_date DATETIME = '2015-02-12 00:00:00.000'; DECLARE @end_date DATETIME = '2015-02-13 00:00:00.000'; WITH AllDays AS ( SELECT @start_date AS [Date], 1 AS [level] UNION ALL SELECT DATEADD(DAY, 1, [Date]), [level] + 1 FROM AllDays WHERE [Date] < @end_date ) SELECT [Date], [level] FROM AllDays OPTION (MAXRECURSION 0) pass the @start_date and @end_date as SP parameters.
Result:
Date level ----------------------- ----------- 2015-02-12 00:00:00.000 1 2015-02-13 00:00:00.000 2 (2 row(s) affected) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
