0

I need to create a new field in my select query which selects the Next first Monday of April if the date is greater than '2003-03-31' is that possible.

For example i have a date 2014-04-21 so I need the calculated date to return as 2015-04-6.

Second example would be 2020-01-13 so the calculated date would need to return 2020-04-6

UPDATED

Data is below:

enter image description here

What I have been attempting is:

       CASE
       WHEN Date <= '2003-03-31' THEN
           '2004-04-5'
       WHEN Date > '2003-03-31' THEN 
       ????? (First Monday of the next April)
       END AS 'Test1',

This is where I m stuck sorry I was so vague.

ryall579
  • 83
  • 1
  • 7
  • Show us some sample table data and the expected result. (As formatted text, not images.) – jarlh Jan 13 '20 at 12:27
  • 1
    Please explain why the second example gives `2010-04-6` – Squirrel Jan 13 '20 at 12:31
  • Getting the next first of April is easy with DATEFROMPARTS as only the year component needs to be dynamic. Then a duplicate of https://stackoverflow.com/questions/18920393/sql-server-get-next-relative-day-of-week-next-monday-tuesday-wed – Martin Smith Jan 13 '20 at 12:36
  • If you substract DayOfWeek days from a date you get the first week date when weeekdays in 0..6, and last date of prev. week if 1..7. So extract the year, add Apr 7 (or 8), substract its weekday. If given date is after calculated one, add one more year and repeat. – Akina Jan 13 '20 at 12:43
  • Are you sure for the `2020-01-13` the expected result is `2010-04-6`? or it should be `2020-04-6`? – Arulkumar Jan 13 '20 at 12:55
  • @Squirrel My mistake was meant to put 2020, just a typo on my end. – ryall579 Jan 13 '20 at 13:33

1 Answers1

1

As I believe that you have written a small mistake in your question :

Second example would be 2020-01-13 so the calculated date would need to return 2010-04-6

You wanted to write :

Second example would be 2020-01-13 so the calculated date would need to return 2020-04-6

Here is how you can do it:

SELECT Date_c
       , case when  Date_c >= DATEADD(DAY, (9 - DATEPART(dw,CONVERT(date,concat(year(Date_c), '-04-01')))), CONVERT(date,concat(year(Date_c), '-04-01')))
              then DATEADD(DAY, (9 - DATEPART(dw,CONVERT(date,concat(year(Date_c)+1, '-04-01')))), CONVERT(date,concat(year(Date_c)+1, '-04-01'))) 
              else DATEADD(DAY, (9 - DATEPART(dw,CONVERT(date,concat(year(Date_c), '-04-01')))), CONVERT(date,concat(year(Date_c), '-04-01')))
              end as NEXTMONDAY
from testT
where Date_c > '2003-03-31';

Here is the DEMO

VBoka
  • 8,995
  • 3
  • 16
  • 24
  • Thanks for that, the case I had started is: CASE WHEN Date <= '2003-03-31' THEN '2004-04-5' WHEN Date > '2003-03-31' THEN END AS 'Test1', How do I add yours to it only because I cant add a where clause to the entire script as I would miss data. – ryall579 Jan 13 '20 at 14:03
  • Something like this: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b83478af73cbb29973bedaa67bf55bce – VBoka Jan 13 '20 at 14:13
  • P.S. If the answer is of any help you can give it a vote up. Then if there is also a correct answer then you can mark it as correct. Also, please do add to your question the fact that you can not use the where part. – VBoka Jan 13 '20 at 14:15
  • 1
    I have seen now your question update. Here is a new proposition: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d18946a096a368cc085b3b496e0b2745 – VBoka Jan 13 '20 at 14:16