SQL create a DateTime value from Year and Quarter

I know the year and the quarter (e.g. "2010" and "4") for a schedule-related milestone and I want to select/create a datetime from it. There are a number of nifty ways to identify the quarter with formats ("qq") of a particular date, but not to go the other way around (or are there?). This is with t-sql / SQL Se开发者_如何转开发rver.

Note: the datetime should be for the last day of that quarter.

UPDATE: Here is the solution that I ended up using courtesy of gbn, with AaronLS's variable names and then shortened-and-sweetened with Frank Kalis' suggestion :-) It was important to test for all 4 quarters to make sure the year is handled properly. Thanks to everyone who answered!

DECLARE @TheQuarter INT
DECLARE @theYear INT
-- Note: qq = q = quarter for the datepart
SET @TheQuarter = 1
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-03-31 00:00:00.000

SET @TheQuarter = 2
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-06-30 00:00:00.000

SET @TheQuarter = 3
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-09-30 00:00:00.000

SET @TheQuarter = 4
SET @TheYear = 2011
SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))
-- 2011-12-31 00:00:00.000

Here are a few q's that fetch the quarter from the date but not the other way around: Calculate the Last Day in the CURRENT quarter; Calculate the last day of the quarter; Best way to store quarter and year in SQL Server?

Never use strings for datetime conversions: too much to go wrong with formats, language etc.

Keep it in the datetime type...

Select dateadd(day, -1, 
                       dateadd(year, @year-1900,
                                          dateadd(quarter, @qq, 0)
                                     )
             )

Looks like you've already found your solution, but just for the sake of it... If you choose a different base date, you can shorten the whole thing to

SELECT DATEADD(YEAR, @TheYear-1900, DATEADD(qq, @TheQuarter, -1))

Since 0 indicates SQL Server's base date of 01.01.1900 (and the first day of a month), using -1 as base date starts off 1 day earlier and then you already have your last day of a month (and end of a quarter). Then you just need to do the rest of the datetime magic and voilà.

Just choose the date from the quarter:

select
    case @theQuarter
        when 1 then '3/31/' + cast(@theYear as varchar(4))
        when 2 then '6/30/' + cast(@theYear as varchar(4))
        when 3 then '9/30/' + cast(@theYear as varchar(4))
        when 4 then '12/31/' + cast(@theYear as varchar(4))
    end as quarterDate

Edit: Adjusted to be last day of quarter instead of first day.

This basically gets the first day of the following quarter, and then subtracts one so that you have the last day of the quarter you wanted. (@theQuarter + 1) adds one to the quarter, then *3 -2 gets the first month of that quarter, and % 12 is required when for the fourth quarter because you add one to 4 to get 5, which gives you 13 but you really want 1, so the % takes care of that.

Finally after casting it all to a date time, we have the first day of the following quarter, thus subtract - 1 at the end to subtract one day and get the last day of the quarter we initially put in.

declare @theQuarter as int;
set @theQuarter = 4;

declare @theYear as int;
set @theYear = 2009;

select 
cast(
  cast(
        ( (@theQuarter + 1) * 3 - 2) % 12 
  as varchar(2)) 
  + '-01-' 
  + cast( (@theYear + (((@theQuarter + 1) * 3 - 2)/ 12) ) as varchar(4))  
as datetime) - 1 ;