TSQL: Get the first date of a quarter

Here’s a script to find the first calendar date in the quarter, given any date.

So, given March 3, 2011, we want to return January 1, 2011, the first date of Quarter 1.

declare @date datetime

select @date = '03/03/2011'

select @date,

           cast(   cast(((DATEPART(QUARTER, @date)- 1) *3) + 1 as CHAR(2))

                     + '/01/'

                     + cast(DATEPART(year, @date) as CHAR(4))

           as datetime)

-----

2011-03-03 00:00:00.000    2011-01-01 00:00:00.000

As a demonstration, we can wrap that call in a loop and display the results of the formula for a full year.

declare @date datetime

select @date = '01/01/2011'

while @date <= '01/01/2012'

begin

select @date,

           cast(   cast(((DATEPART(QUARTER, @date)- 1) *3) + 1 as CHAR(2))

                     + '/01/'

                     + cast(DATEPART(year, @date) as CHAR(4))

           as datetime)

select @date = DATEADD(month, 1, @date)

end

Or, we may the first date of the next quarter, so April 1 2011.  This is easy, just add 3 months:

declare @date datetime

select @date = '03/03/2011'

select @date,

     DATEADD(month, 3,

           cast(   cast(((DATEPART(QUARTER, @date)- 1) *3) + 1 as CHAR(2))

                     + '/01/'

                     + cast(DATEPART(year, @date) as CHAR(4))

           as datetime)

     )

-----

2011-03-03 00:00:00.000    2011-04-01 00:00:00.000

One final note: when doing date calculations like this in a report, where you don’t care about time, in SQL 2008 you can use the date datatype for your variables and casts instead of datetime.  It makes for a cleaner, more compact script and reduces the need for all those zeroes.

-----

2011-03-03 2011-04-01