Created
March 25, 2015 18:05
-
-
Save wilwang/00e4ade121fabcd3864f to your computer and use it in GitHub Desktop.
Get a table of Month Ends between two dates without using a LOOP
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @StartDate = CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(@StartDate)) + '/1/' + CONVERT(VARCHAR, YEAR(@StartDate))); | |
| -- get a table of all of the month ends between @startDate and @endDate | |
| CREATE TABLE #MonthEnds ( | |
| MonthEnd DATETIME | |
| ); | |
| INSERT INTO #MonthEnds (MonthEnd) | |
| SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate)+1) | |
| DATEADD( | |
| DAY, | |
| -1, | |
| DATEADD( | |
| MONTH, | |
| ROW_NUMBER() OVER(ORDER BY object_id), | |
| @StartDate | |
| ) | |
| ) | |
| FROM sys.all_objects -- used primarily to generate a Numbers table |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment