• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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
0
ravichand-sql
Asked:
ravichand-sql
3 Solutions
 
dsackerContract 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 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
 
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
 
ThomasianCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now