Finding sql date with account year and Quarter

Question

I am new in sql i am facing one problem I need to create stored procedure which return start and end date of any quarter by passing Quarters as string string and year

ex : when i pass exec spname '2014-15','jul-sep', it should start date of july and end date of September

Answer 1

It is unclear why you do not provide only year and quarter as input parameters. The following procedure should return the quarter beginning and end:

alter procedure spQuarter (
    @yearStr VARCHAR(7),
    @QuarterStr VARCHAR(20)
)
AS
BEGIN
    DECLARE @year INT = CAST(SUBSTRING(@yearStr, 1, 4) AS INT)

    DECLARE @QuarterId INT = (CASE
        WHEN @QuarterStr = 'jan-mar' THEN 4
        WHEN @QuarterStr = 'apr-jun' THEN 1
        WHEN @QuarterStr = 'jul-sep' THEN 2
        WHEN @QuarterStr = 'oct-dec' THEN 3
    END)

    DECLARE @startOfYStr VARCHAR(20) = CAST (@year AS VARCHAR) + '01' + '01'

    PRINT @yearStr
    PRINT @startOfYStr

    DECLARE @startDate DATE = CAST(@startOfYStr AS DATE)
    DECLARE @startOfQ DATE = DATEADD(quarter, @QuarterId, @startDate)
    DECLARE @endOfQ DATE = DATEADD(day, -1, DATEADD(quarter, @QuarterId + 1, @startDate))

    SELECT @startOfQ, @endOfQ
END
GO

Tests:

exec spQuarter '2014-15', 'jan-mar' --> 2015-01-01  2015-03-31
exec spQuarter '2014-15', 'apr-jun' --> 2014-04-01  2014-06-30
exec spQuarter '2014-15', 'oct-dec' --> 2014-10-01  2014-12-31

Some extra checking should be performed on input parameters, but it should be a good start for you need.