I have a year's worth of inventory data, and I want to extract the first and last day of each month's data. What is the best way to do this?
That is a pretty wide-open description and could use more details if there are complications in the data you are using. But here is a basic idea.
First day of month is easy: YYYYMM01
Last day of month can easily be calculated by:
DECLARE @FirstDayOfMonth DATETIME; -- Use DATETIME for older SQL Server versions SET @FirstDayOfMonth = '20150601' -- Safest format for dates. SELECT DATEADD(DAY,-1,(DATEADD(MONTH,1,@FirstDayOfMonth)));
This trivializes finding the last day of the month. It is one day before the first day of the next month. No need to calculate leap years, lengths of months, et cetera.
Following on from @RLF's solution, an alternative solution for the last day of the month for SQL Server 2012 and above is to use the new
If you are using SQL Server 2008 R2 or lower, this will not work for you.
DECLARE @FirstDayOfMonth DATE; SET @FirstDayOfMonth = '20150601'; SELECT EOMONTH(@FirstDayOfMonth);