How to get quarter startdate and enddate based on input values(YEAR and QUARTER)

Question

0.00/5 (No votes)

See more:

Need a SQL function or stored procedure which returns the quarter start date and end date based input parameters (QUARTER AND YEAR)

Example:
if I pass 2020 and Q2 as parameter
result should be

2020-07-01 as  START_DATE,  2020-09-30 as END_DATE

What I have tried:

I am using this for to get the employee list on these dates

select * from tblEmployeeMaster
where convert(datetime, DOL, 103) between '2020-07-01' and '2020-09-30'---Q2 11 left

select * from tblEmployeeMaster
where convert(datetime, DOJ, 103) between '2020-07-01' and '2020-09-30'----Q2 20 joined

Posted 18-Jan-21 19:01pm

Virendra S from Bangalore, Karnataka

Updated 18-Jan-21 20:38pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Accepted Answer · 2021-01-18T19:50:00

Solution 1

SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))

And from quarter and year variables:

DECLARE @MyYear INT = 2021;
DECLARE @MyQuarter INT = 1;
DECLARE @MyDate DATE;

-- Get date from quarter and year
SET @MyDate = DATEADD(qq, @MyQuarter - 1, DATEFROMPARTS(@MyYear, 1, 1))
SELECT @MyDate

-- Last day of Quarter
SELECT DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, @MyDate) +1, 0))

Posted 18-Jan-21 19:50pm

RickZeeland

Updated 18-Jan-21 20:20pm

v2

Comments

Virendra S from Bangalore, Karnataka 19-Jan-21 2:17am

can you please elaborate the answer.
how to pass the year and quarter parameter here

Maciej Los 19-Jan-21 2:45am

5ed!

RickZeeland 19-Jan-21 2:50am

Thanks, sadly no beer icon here :)

Maciej Los 19-Jan-21 3:41am

We can use "tag" => :beer:
or
we can copy-paste emoticon => 🍺

:D

RickZeeland 19-Jan-21 3:46am

A miracle! 👏

Maciej Los 19-Jan-21 4:16am

:D

Maciej Los · Accepted Answer · 2021-01-18T20:38:00

Quote:
if I pass 2020 and Q2 as parameter
result should be
2020-07-01 as  START_DATE,  2020-09-30 as END_DATE

Nope. The result should be 2020-04-01 - 2020-06-30.

As to the second parameter Q2... I'd pass only a number (2).

SQL

-- parameters
DECLARE @year int = 2020;
DECLARE @qu int = 2;

-- body of SP
DECLARE @tmp TABLE (iQrt int, begMonth int, endMonth int);

INSERT INTO @tmp (iQrt, begMonth, endMonth)
VALUES(1, 1, 3), (2, 4, 6), (3, 7, 9), (4, 10, 12);

SELECT  DATEFROMPARTS(@year, begMonth, 1) StartDate
  , DATEADD(DD, -1, DATEFROMPARTS(@year, endMonth+1, 1)) EndDate
FROM @tmp
WHERE iQrt = @qu;

You can test it via SQL Server 2019 | db<>fiddle[^]

There's an idea. Now, use above code to write stored procedure. Good luck!

For further please, see:
DATEFROMPARTS (Transact-SQL) - SQL Server | Microsoft Docs[^]
Create a Stored Procedure - SQL Server | Microsoft Docs[^]