First and last date of the current month

How do I get the first date and the last date of the current month?

As with all SQL questions related to dates, the answer depends on which database system you're using, since they all have different syntax and functions for dealing with temporal datatypes.

Here are two good algorithms, which are quite efficient as well.

For the first day of the current month, start with today, for which every database system has a function, called getdate(), date(), current_date, or whatever. Use another function, usually called day(), to get the day of the month from today. So if today is the 12th of March, then the day of the month is 12. Now subtract 1 from that number, to get 11, and then subtract that number of days from today. Voila, the first day of the current month.

Here it is using SQL Server syntax:

select 
  dateadd( dd           -- add days
   , -day(getdate())+1  -- how many 
   , getdate()          -- to this date
         )

See the SQL Server documentation for dateadd(), day(), and getdate() for full details on how they work. Other database systems have similar functions. Please do write to me if you have trouble adapting these algorithms to your system.

The last day of the current month is similar, but trickier. Get the first day of the month as above, then add 1 month to it, then subtract 1 day:

select 
  dateadd( dd
   , -1
   , dateadd( mm
      , 1
      , dateadd( dd
         , -day(getdate())+1
         , getdate()
               )
            )
         )

Note that you have to do it in exactly that sequence: you can't, for example, subtract the number of days from today to get the last day of the previous month, and then just add 1 month, because the last day of February is the 29th, and adding one month would give you March 29th, which is wrong.

For More Information

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.