asked on

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...!!!

SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chaau

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

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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

SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial