• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

List of Months and Quarters for given date range

Hello,

I want a function that takes Start date and End date, and should return me a result set containing Year, Month number, Month Name, Quarter Number, and Quarter Name for each of the Year in that given date range.

How to achieve this?

Thanks!
0
sath350163
Asked:
sath350163
3 Solutions
 
PortletPaulfreelancerCommented:
what does this bit mean? "for each of the Year in that given date range."
perhaps you could provide some examples?

Do you have any tables to use in this? (You most probably should have.)
try this EE article by Jim Horn
Date-Fun-Part-One-Build-your-own-SQL-calendar-table-to-perform-complex-date-expressions

details:
is "month number" simply 1 to 12 based on the calendar month of any date? (i.e. the month number isn't a financial calendar is it?)

& can we assume quarters aren't related to a financial calendar also.
0
 
David ToddSenior DBACommented:
Hi,

select
    dateadd( year, datediff( year, 0 , getdate()), 0 ) as StartOfYear
    , dateadd( quarter, datediff( quarter, 0, getdate()), 0 ) as StartOfQuarter
    , dateadd( month, datediff( month, 0, getdate()), 0 ) as StartOfMonth

HTH
  David
0
 
Scott PletcherSenior DBACommented:
The function is below.  Not sure what you meant by "quarter name", so change it as required:


--sample usage of function after it is created:
--SELECT * FROM dbo.Qtrs_and_Months ( '20130623 11:42:17', getdate()) order by year, month
GO

CREATE FUNCTION dbo.Qtrs_and_Months (
    @start_date datetime,
    @end_date datetime
)
RETURNS TABLE
AS
RETURN (
WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
)
SELECT
    YEAR(current_month) AS Year,
    DATEPART(MONTH, current_month) AS Month,
    DATENAME(MONTH, current_month) AS Month_Name,
    DATEPART(QUARTER, current_month) AS Quarter,
    'Quarter' + CAST(DATEPART(QUARTER, current_month) AS varchar(1)) AS Quarter_Name
FROM (
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0) AS start_month,
        DATEADD(MONTH, DATEDIFF(MONTH, 0, @end_date), 0) AS end_month
) AS param_values
INNER JOIN cteTally t ON
    t.tally BETWEEN 0 AND DATEDIFF(MONTH, start_month, end_month)
CROSS APPLY (
    SELECT DATEADD(MONTH, t.tally, start_month) AS current_month
) AS ca1
)

GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now