5.5 Deriving the First and Last Dates of the Month

5.5.1 Problem

Given an arbitrary date, find the first and last day of the month in which that date falls.

5.5.2 Solution

Use the following query in which CURRENT_TIMESTAMP represents the arbitrary input date:

SELECT 
   CONVERT(CHAR(8),CURRENT_TIMESTAMP,120)+'01' First_date,
   CAST(SPACE(
      DATEPART(weekday,
         CONVERT(CHAR(8),CURRENT_TIMESTAMP,120)+'01'
      )-1)+'*' as CHAR(8)) "SMTWTFS",
   CONVERT(CHAR(10),
      DATEADD(day,-1,
         DATEADD(month,1,CONVERT(CHAR(8),CURRENT_TIMESTAMP,120)+'01')
      ),120) Last_date,
   CAST(SPACE(
      DATEPART(weekday,
         DATEADD(day,-1,DATEADD(m,1,
            CONVERT(CHAR(8),CURRENT_TIMESTAMP,120)+'01')
         )
      )-1)+'*' AS CHAR(8)) "SMTWTFS "

This query is a bit complicated because rather than just return two date values, we've chosen also to return a graphical indication of the day-of-the-week on which the first and last dates of the month fall:

First_date SMTWTFS  Last_date  SMTWTFS  
---------- -------- ---------- -------- 
2001-07-01 *        2001-07-31   *     

We ran this query sometime during July, 2001. You can see that the first day of the month was July 1 (obvious) and that the last day of the month was July 31. The other two columns of output indicate that the first day of the month fell on a Sunday, while the last day of the month fell on a Tuesday.

It may seem obvious that the first day of any month is the first, but we've often found it necessary to write SQL queries that automatically calculate that date based on any arbitrary input date.

We've used CURRENT_TIMESTAMP in this solution to provide an input date value. However, you can replace CURRENT_TIMESTAMP with any arbitrary date value, whether from a function, a column, a bind variable, or even a constant.

5.5.3 Discussion

Transact-SQL is quite strong in that it offers many ways to manipulate information about one point in time. The query in this recipe is a typical example of what you can do using Transact-SQL's built-in date functionality.

The first item in the query's select list translates the input date into a string to cut off the day part, which is then replaced with the constant '01':

CONVERT(CHAR(8), CURRENT_TIMESTAMP, 120)+'01'

The result of this expression is a string such as '2001-07-01'. The CONVERT function can return results in several different styles. Style 120, which we use here, is ISO compliant and takes the form YYYY-MM-DD HH:MI:SS. We keep just the first eight characters, resulting in a value in YYYY-MM- format. Then we add the '01' string, and the result is the date of the first day of the month.

To find out the day of the week on which a given day falls, we use the DATEPART function with the weekday parameter. That function returns 1 for Sunday, 2 for Monday, and so on. To print the result in a graphical format, we used the numeric day of the week value, along with the SPACE function to set the asterisk (*) in the right spot:

SPACE(
      DATEPART(weekday,
         CONVERT(CHAR(8),CURRENT_TIMESTAMP,120)+'01'
      )-1)+'*'

Calculating the last day of the month is trickier. The following logic is used in the query:

DATEADD(day,-1,DATEADD(month,1,
   CONVERT(CHAR(8),CURRENT_TIMESTAMP,120)+'01')

We take the current date (retrieved by CURRENT_TIMESTAMP), set it to the first day of the month, add one month to the result to get the first day of the following month, and, finally, subtract one day to get the last day of the current month.

There is one other little trick to this query that we should point out. Notice that we appear to have two columns of output labeled "SMTWTFS". As you know, SQL Server won't let you use two labels with the same name. A trick you can use in such cases is to add an additional space to the second label. To the server, "SMTWTFS" and "SMTWTFS " are not the same labels, but, to the user, they look the same.