StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>declare @T table ( START_DATE date ) insert into @T values ('2012-05-01'), ('2012-02-15'), ('2012-06-15') select dateadd(month, 2, dateadd(quarter, datediff(quarter, 0, dateadd(month, case when day(START_DATE) >= 15 then 1 else 0 end, START_DATE)), 0)) from @T </code></pre> <p><strong>Update:</strong></p> <p>The main part of this query is to get the first date of a quarter and that is done by the dateadd/datediff trick.</p> <pre><code>select dateadd(quarter, datedifF(quarter, 0, START_DATE), 0) from @T </code></pre> <p>Result:</p> <pre><code>----------------------- 2012-04-01 00:00:00.000 2012-01-01 00:00:00.000 2012-04-01 00:00:00.000 </code></pre> <p>You wanted the last month of the quarter so we add a <code>dateadd(month, 2</code></p> <pre><code>select dateadd(month, 2, dateadd(quarter, datedifF(quarter, 0, START_DATE), 0)) from @T </code></pre> <p>Result:</p> <pre><code>----------------------- 2012-06-01 00:00:00.000 2012-03-01 00:00:00.000 2012-06-01 00:00:00.000 </code></pre> <p>That leaves only the part where dates later than the 15th of the last month should be in the end of the next quarter. <code>day(START_DATE)</code> will give you the day of the month so we can add 1 month to START_DATE if <code>day(START_DATE) >= 15</code> using a case statement.</p> <pre><code>select dateadd(month, case when day(START_DATE) >= 15 then 1 else 0 end, START_DATE) from @T </code></pre> <p>Result:</p> <pre><code>---------- 2012-05-01 2012-03-15 2012-07-15 </code></pre> <p>Putting it all together it will look like this.</p> <pre><code>select dateadd(month, 2, dateadd(quarter, datedifF(quarter, 0, dateadd(month, case when day(START_DATE) >= 15 then 1 else 0 end, START_DATE)), 0)) from @T </code></pre> <p>Result:</p> <pre><code>----------------------- 2012-06-01 00:00:00.000 2012-03-01 00:00:00.000 2012-09-01 00:00:00.000 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload