Help with Date query

Hello Experts!!

I need your help in writing a query to populate a date for the below scenario.

For a given quarter date, I want to get first day of the first month of the quarter and last day of the last month of the quarter.

For Example... If my quarter date is : 03-01-2013, then my startdate should be 01-01-2013 and enddate should be 03-31-2013

Likewise,

QuarterDate:                  FirstDate:                             LastDate:
03-01-2013                     01-01-2013                          03-31-2013
06-01-2013                     04-01-2013                          06-30-2013

Thanks in advance...!!!


CalendarDate
ravichand-sqlAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
This should do it:
SELECT	DATEADD(quarter, DATEPART(quarter, QuarterDate) - 1, DATEADD(day, 1 - DATEPART(dayofyear, QuarterDate), QuarterDate)),
	DATEADD(day, -1, DATEADD(quarter, DATEPART(quarter, QuarterDate), DATEADD(day, 1 - DATEPART(dayofyear, QuarterDate), QuarterDate)))

Open in new window

0
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
This should do the trick:

SELECT  CalendarDate,
        CONVERT(datetime, CONVERT(varchar, (DATEPART(q, CalendarDate) * 3) - 2) + '/1/' + CONVERT(varchar, YEAR(CalendarDate)))
                    AS QtrBeg,
        DATEADD(m, 1, CONVERT(datetime, CONVERT(varchar, DATEPART(q, CalendarDate) * 3) + '/1/' + CONVERT(varchar, YEAR(CalendarDate)))) - .00000005
                    AS QtrEnd
FROM    YourTableName WITH (NOLOCK)

Open in new window

I had to fix the code a little bit, so if you grabbed it before you see this message, please grab it again.
0
 
chaauCommented:
Just a small note: this query will only work in America, or in the countries with mm/dd/yyyy date format. Will not work in Europe
0
 
Anthony PerkinsCommented:
This is how I tested it:

DECLARE @QuarterDate date 

SET @QuarterDate = '20130301'
SELECT	DATEADD(quarter, DATEPART(quarter, @QuarterDate) - 1, DATEADD(day, 1 - DATEPART(dayofyear, @QuarterDate), @QuarterDate)),
	DATEADD(day, -1, DATEADD(quarter, DATEPART(quarter, @QuarterDate), DATEADD(day, 1 - DATEPART(dayofyear, @QuarterDate), @QuarterDate)))

SET @QuarterDate = '20130601'
SELECT	DATEADD(quarter, DATEPART(quarter, @QuarterDate) - 1, DATEADD(day, 1 - DATEPART(dayofyear, @QuarterDate), @QuarterDate)),
	DATEADD(day, -1, DATEADD(quarter, DATEPART(quarter, @QuarterDate), DATEADD(day, 1 - DATEPART(dayofyear, @QuarterDate), @QuarterDate)))

Open in new window

0
 
ThomasianConnect With a Mentor Commented:
DECLARE @QuarterDate DateTime
SET @QuarterDate = '2013-06-01'	
SELECT FirstDate=DATEADD(QUARTER,DATEDIFF(QUARTER,0,@QuarterDate),0)
      ,LastDate=DATEADD(QUARTER,DATEDIFF(QUARTER,0,@QuarterDate)+1,-1)

Open in new window

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.

All Courses

From novice to tech pro — start learning today.