# Find first day and last day+1 day of a quarter [Answered]RSS

## 2 replies

Last post May 15, 2015 09:18 AM by limno

• sushsudh

Member

27 Points

181 Posts

### Find first day and last day+1 day of a quarter

Suppose i have 4Q2014 Archive,3Q2014 Archive,2Q2014 Archive,1Q2014 Archive.

4Q2014 Archive i want to get the first day of that quarter 2014-10-01 and last day + 1day will be 2015-01-01

quarter

• A2H

All-Star

50131 Points

9721 Posts

### Re: Find first day and last day+1 day of a quarter

#### sushsudh

Suppose i have 4Q2014 Archive,3Q2014 Archive,2Q2014 Archive,1Q2014 Archive.

4Q2014 Archive i want to get the first day of that quarter 2014-10-01 and last day + 1day will be 2015-01-01

You can use the DateAdd function to get the Quarter start and end date

You can try with the below code

DECLARE @quarterDate [datetime]
--Set the value here
SET @quarterDate =Convert(varchar(30),'10/11/2014',102)
--Get the first and last date
SELECT CONVERT(varchar(10), DATEADD(qq, DATEDIFF(qq, 0, @quarterDate), 0),126) as StartDate,

quarter

Thanks,
A2H
My Blog | Dotnet Funda
• limno

All-Star

122408 Points

9756 Posts

Moderator

### Re: Find first day and last day+1 day of a quarter

Create table test (id int, YYYYQ varchar(14)
)

Insert into test values(1,'1Q2014 Archive'),
(2,'2Q2014 Archive'),
(3,'3Q2014 Archive'),
(4,'4Q2014 Archive'),
(5,'1Q2015 Archive')

SELECT

Cast( Substring(YYYYQ ,3,4)+ Right('0'+Cast((Left(YYYYQ,1)-1)*3+1 as varchar(2)),2) +'01' as Date)  FirstdayofTheQuarter,

Cast( Substring(YYYYQ ,3,4)+ Case when (Left(YYYYQ,1)-1)  % 3 =0
Then Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'31'
Else Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'30' END  as Date)   LastdayofTheQuarter,

Substring(YYYYQ ,3,4)+ Case when (Left(YYYYQ,1)-1)  % 3 =0
Then Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'31'
Else Right('0'+Cast(Left(YYYYQ,1)*3 as varchar(2)),2) +'30' END) LastdayofNextQuarter

From test

---Or

Select
Substring(YYYYQ ,3,4)+  Right('0'+Cast(((Cast(Left(YYYYQ,1) as int)-1) *3 +1)as varchar(2)),2)+'01' FirstdayofTheQuarter
,
EOMOnth( Substring(YYYYQ ,3,4)+  Right('0'+Cast(Cast(Left(YYYYQ,1) as int) *3 as varchar(2)),2)+'01' ) LastdayofTheQuarter
,
Dateadd(day,1,EOMOnth( Substring(YYYYQ ,3,4)+  Right('0'+Cast(Cast(Left(YYYYQ,1) as int) *3 as varchar(2)),2)+'01' ))
FirstdayofNextQuarter

From test

Drop table test

quarter

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm