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?

  • 2
    What version of SQL server are you using? – KASQLDBA Jun 19 '15 at 13:13

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.

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.

  • It's throwing me an error reading: Msg 2715, Level 16, State 3, Line 3 Column, parameter, or variable #1: Cannot find data type DATE. Parameter or variable '@FirstDayOfMonth' has an invalid data type. – Nick Jun 19 '15 at 13:40
  • Updated to use the DATETIME data type. Included @ypercubes commend on date format of YYYYMMDD. – RLF Jun 19 '15 at 13:47

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 EOMONTH() function.

If you are using SQL Server 2008 R2 or lower, this will not work for you.

DECLARE @FirstDayOfMonth DATE; 
SET @FirstDayOfMonth = '20150601';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.