Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get last date of a given quarter

Posted on 2008-10-14
18
Medium Priority
?
1,028 Views
Last Modified: 2009-06-23
I want to get the last date of a given quarter. The quarter is provided as an integer.

I was going down the road of using this SQL but doesnt seem to work:

SELECT DATEADD(m, 1, CONVERT(DATETIME, CAST(DATEPART(year, GETDATE()) AS varchar) + '-' + CAST(@Quarter * 3 AS varchar) + '-' + '01', 102)) AS DateQuarterEnd

I get the error: "String was not recognised as a valid date time"

Which is strange because all im doing is adding a month to the result of this query which does work:

SELECT CONVERT(DATETIME, CAST(DATEPART(year, GETDATE()) AS varchar) + '-' + CAST(@Quarter * 3 AS varchar) + '-' + '01', 102) AS DateQuarterStart
0
Comment
Question by:g-spot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
18 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22710513
Well, I'm not getting that error for any quarter between 1 & 4.  I do find that it's actually giving the first date of the NEXT quarter, e.g. 2008-04-01 for Q1.

One more DateAdd gets it working here ...

SELECT DateAdd(d, -1, DATEADD(m, 1, CONVERT(DATETIME, CAST(DATEPART(year, GETDATE()) AS varchar) + '-' + CAST(@Quarter * 3 AS varchar) + '-' + '01', 102))) AS DateQuarterEnd

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22710647
Daniel is correct.  The reason is you are taking the quarter say 3 and multiplying by 3 and casting as a date with a literal '01', so now you have 09/01/2008.  If you add a month, you are at 10/01/2008 by design.
0
 

Author Comment

by:g-spot
ID: 22710932
Hi mwvisa and Daniel

Thanks for your advice.

Youre right, I am at 10/01/2008 by design. In fact I'm at 00:00 hours on 10/01/2008 which means i'm including all of 09/30/2008.

If I subtract one day then im at 00:00 on the 09/30/2008 which means I lose all that day.

I've just realised that my original code does work and the error message only appears in Visual Studio. The query works when run in Management Studio

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 504 total points
ID: 22710969
>>Youre right, I am at 10/01/2008 by design. In fact I'm at 00:00 hours on 10/01/2008 which means i'm including all of 09/30/2008.
To handle that, I usually subtract 1 second so I go through 9/30 11:59:59 PM.


SELECT DateAdd(s, -1, DATEADD(m, 1, CONVERT(DATETIME, CAST(DATEPART(year, GETDATE()) AS varchar) + '-' + CAST(@Quarter * 3 AS varchar) + '-' + '01', 102))) AS DateQuarterEnd

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 496 total points
ID: 22716406
I think you can simplify it:

SELECT DATEADD(QUARTER, @quarter, CAST(YEAR(GETDATE()) AS CHAR(4)) + '0101') AS DateQuarterEnd
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22720680
I obviously don't use DateAdd to it's full potential as that is absolutely brilliant.  Gets you right to 10/01 and then you can combine with Daniel's suggestion of using DateAdd(s, -1, somedate) -- putting it together (Scott and Daniel's solutions):

DECLARE @quarter int
SET @quarter = 3

SELECT DATEADD(s, -1, DATEADD(QUARTER, @quarter, CAST(YEAR(GETDATE()) AS CHAR(4)) + '0101')) AS DateQuarterEnd
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22720812
Actually subtracting 1 second is not correct.  The problem is, for a DATETIME, it should be 3ms, for a SMALLDATETIME, one minute.

Of course that's a pain to do.  So the easiest thing is usually to instead use < than the next day.

For example, if I want all records for the current date, instead of this:

-- not recommended
WHERE columnDate BETWEEN CONVERT(CHAR(8), GETDATE(), 112) AND DATEADD(SECOND, -1, CONVERT(CHAR(8), DATEADD(DAY, 1, GETDATE()), 112))

-- recommended, if possible
WHERE columnDate >= CONVERT(CHAR(8), GETDATE(), 112) AND columnDate < CONVERT(CHAR(8), DATEADD(DAY, 1, GETDATE()), 112)


0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 496 total points
ID: 22720887
CORRECTION:  re-edited comments from last posting to more accurately reflect what I was trying to say
:
This is a common and annoying problem.  Technically subtracting 1 second will not work.

For example, if you subtract 1 second, you get back to <yyyymmdd> 23:59:59.  But that means you will miss times between 23:59:59.003 and 23:59:59.997.

The problem is, for a DATETIME, it should be 3ms, for a SMALLDATETIME, one minute.  Of course that's a pain to do, and worse, the datatype could change.  So the cleanest thing is usually to instead use < than the next day.

For example, if I want all records for the current date, instead of this:

-- not recommended
WHERE columnDate BETWEEN CONVERT(CHAR(8), GETDATE(), 112) AND DATEADD(SECOND, -1, CONVERT(CHAR(8), DATEADD(DAY, 1, GETDATE()), 112))

-- recommended, if possible
WHERE columnDate >= CONVERT(CHAR(8), GETDATE(), 112) AND columnDate < CONVERT(CHAR(8), DATEADD(DAY, 1, GETDATE()), 112)
0
 

Author Comment

by:g-spot
ID: 22721065

"The problem is, for a DATETIME, it should be 3ms, for a SMALLDATETIME, one minute.  Of course that's a pain to do, and worse, the datatype could change.  So the cleanest thing is usually to instead use < than the next day."

Which is basically what I was suggesting in the first place by getting the first day of the following quarter.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22721086
Agreed.  Personally, I will at times just say < the date I come up with and it being midnight covers me without including anything from the next day/quarter.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22721136
LOL, you sure did.  :) See Scott's post then on DateAdd with Quarter -- works exactly as needed.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22721260
I'm usually actually using a BETWEEN statement ... and data from some sources are really date-only, e.g. 2008-10-15 00:00:00 -- which would be included in a BETWEEN that used the start of a day over as the upper bound.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22721611
But BETWEEN includes the ending value, so you could get a value from the next day at precisely midnight.  For a SMALLDATETIME, that is a very real possibility, for a DATETIME, still possible, atlhough much less likely.
0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 504 total points
ID: 22721784
>> for a DATETIME, still possible, atlhough much less likely.

Assuming they are actual recorded Date/Times, that's true.  

But in many systems a date can be keyed in free-form and only the date is taken.  In many other cases dates are imported from legacy systems that have only the date (often as an 8-digit numeric field) and again, we end up with exactly midnight as the data in the SQL Server DB.


0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 22721820
Quite right, excellent point.  I hadn't thought about that.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22721850
That's where we benefit from each others' experience.  I've profited from many of your answers & comments.
0
 

Author Comment

by:g-spot
ID: 22732026
Thanks for all your help on this.

So if I wanted to get everything up to the end of the previous quarter I could use this:
SELECT * FROM Example WHERE EntryDate < DATEADD(QUARTER, DATEPART(QUARTER,DATEADD(m,-3,GETDATE())), CAST(YEAR(DATEADD(m,-3,GETDATE())) AS CHAR(4)) + '0101')

Open in new window

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question