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 Server.

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?

回答1:

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)
                                     )
             )

回答2:

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à.

回答3:

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.

回答4:

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 ;