Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2010-05-18 : 11:06:51
|
| Hello,I am using the following query to pass in a date such as '31 Dec 2009' Then I would like to get the last day of the following quarter.So the result should be '31 March 2010'How do I alter this query to do what I want please?NOTE that this has to work for any date so that the next quarter date is shown.Thanksset dateformat dmy declare @InputDate datetime declare @EndOfQrtDate smalldatetime set @InputDate = '31 Dec 2009' SET @EndOfQrtDate = CAST(YEAR(@InputDate) AS VARCHAR(4)) + CASE WHEN MONTH(@InputDate) IN ( 1, 2, 3) THEN '/30/06' WHEN MONTH(@InputDate) IN ( 4, 5, 6) THEN '/30/09' WHEN MONTH(@InputDate) IN ( 7, 8, 9) THEN '/31/12' WHEN MONTH(@InputDate) IN (10, 11, 12) THEN '/31/03' END select @EndOfQrtDate |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-05-18 : 11:15:11
|
Will this work for all dates? Not 100% sure, but you can test it out:DECLARE @CurrDate DATETIME = '20091231'SELECT DATEADD(DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @CurrDate) + 2, 0)) |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-18 : 11:16:05
|
select dateadd(quarter, 1, @InputDate)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2010-05-18 : 11:27:41
|
quote:
Originally posted by khtan
select dateadd(quarter, 1, @InputDate)
KH[spoiler]Time is always against us[/spoiler]
This only adds a quarter to the date but I would like to get the last day of the next quarter.Any thoughts please?Thanks |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-18 : 12:00:29
|
select @InputDate = '20091203'select dateadd(quarter, datediff(quarter, 0, @InputDate) + 2, -1)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
date