pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Thursday, March 03, 2011

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

No comments: