Problem solve Get help with specific problems with your technologies, process and projects.

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:

  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:

  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

Dig Deeper on Oracle and SQL