g-spot
asked on
Get last date of a given quarter
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
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
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
DECLARE @quarter int
SET @quarter = 3
SELECT DATEADD(s, -1, DATEADD(QUARTER, @quarter, CAST(YEAR(GETDATE()) AS CHAR(4)) + '0101')) AS DateQuarterEnd
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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.
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.
LOL, you sure did. :) See Scott's post then on DateAdd with Quarter -- works exactly as needed.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Quite right, excellent point. I hadn't thought about that.
That's where we benefit from each others' experience. I've profited from many of your answers & comments.
ASKER
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:
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')
One more DateAdd gets it working here ...
Open in new window