Q
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:

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


This was last published in March 2004

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

Hi. Iam getting trouble in using getdate() etc function on my database.
Through error getdate() must be declare.
Plz help.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close