ndb2702 ndb2702 - 7 months ago 14
SQL Question

How do you create custom quarters based on a given date in SQL?

I want to create a function in SQL that would create custom quarters based on a given date.

Here is the expected result set:

set

So how could I do this?

Answer

Seems like a little bit of a hack, but provides the correct results.

DECLARE @myDate DATETIME; SET @myDate = '3/15/16'
SELECT 
UPPER(LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate), -1)), 3) + '-' 
    + LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 2, -1)), 3)) AS Q1,
UPPER(LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 3, -1)), 3) + '-' 
    + LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 5, -1)), 3)) AS Q2,
UPPER(LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 6, -1)), 3) + '-' 
    + LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 8, -1)), 3)) AS Q3,
UPPER(LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 9, -1)), 3) + '-' 
    + LEFT(DATENAME(MONTH, DATEADD(MONTH, MONTH(@MyDate) + 11, -1)), 3)) AS Q4
Comments