Calculate the first and last day of month

Sometimes is it necessary to calculate the first or the last day of month.

1. 1. calculate the first day of month by subtract the days minus 1
2. 2. add 1 month
3. 3. subtract 1 day for getting the last day of month

SQL code:

DECLARE
@ANY_DATE DATE = '2010-02-10'
,@FIRST_DAY_OF_MONTH DATE
,@LAST_DAY_OF_MONTH DATE

/* subtract n-1 days from current date */
SET @FIRST_DAY_OF_MONTH = DATEADD(DD, (-1) * (DAY(@ANY_DATE) - 1), @ANY_DATE)

/*
- add 1 month to first day of month
- subtract 1 day to get last day of month
*/
SET @LAST_DAY_OF_MONTH = DATEADD(DD, -1, DATEADD(MM, 1, @FIRST_DAY_OF_MONTH))

SELECT
  @ANY_DATE ANY_DATE
, @FIRST_DAY_OF_MONTH FIRST_DAY_OF_MONTH
, @LAST_DAY_OF_MONTH LAST_DAY_OF_MONTH

 

Technorati Tags: sql server