-2

I have this query: 

  update courseRights
         set courseRightsLevelExpires = DATEADD(MM,3,courseRightsLevelExpires)

This works fine but what I actually need is to extend to three months from the first day of next month. For example if the rights are expiring today, May 23rd, I need to update to June 1st + 3 months.

Is it possible to do that in one query?

Update

Because it was flagged as duplicate with another question, I'm updating the content to say that I'm not only looking for the first date of next month but I need to add three months to that date.

halfer
  • 19,824
  • 17
  • 99
  • 186
Laziale
  • 7,965
  • 46
  • 146
  • 262
  • Possible duplicate of [First day of the next month](https://stackoverflow.com/questions/22623971/first-day-of-the-next-month) – Tab Alleman May 23 '18 at 17:24
  • Its not a possible duplicate @TabAlleman my requirements were a bit different as you can see from my question. Thanks – Laziale May 23 '18 at 17:28
  • The difference being that you need to add 3 months? From your posted code it looks like you already know how to do that. – Tab Alleman May 23 '18 at 17:29
  • But I didn't know how to combine both functions which was answered in the answers below – Laziale May 23 '18 at 17:31
  • https://stackoverflow.com/questions/34656367/sql-get-first-day-of-month-3-months-before-current-month?rq=1 – Tab Alleman May 23 '18 at 17:46

3 Answers3

2

To get the first day of next month:

DATEADD(m, DATEDIFF(m, -1, current_timestamp), 0)

Three month's after that:

DATEADD(m,3,DATEADD(m, DATEDIFF(m, -1, current_timestamp), 0))

So, if courseRightsLevelExpires holds the base date you're working from:

DATEADD(m,3,DATEADD(m, DATEDIFF(m, -1, courseRightsLevelExpires), 0))
devlin carnate
  • 8,309
  • 7
  • 48
  • 82
0

You can use eomoth() function :

update courseRights
set courseRightsLevelExpires = 
    dateadd(mm, 3, dateadd(dd, 1, eomonth(courseRightsLevelExpires)));
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

I will do:

set nextmonthdate = last_day (your-date) + 1 day

Bobok Perona
  • 1
  • your solution is not clear enough, add a description of it and tell how it's going to solve the problem, please. – Farzad Karimi Aug 18 '18 at 07:44
  • Here what I wound do: – Bobok Perona Aug 19 '18 at 14:20
  • You can use function LAST_DAY. It will give you the last day of any given date. Adding 1 day to that will give you the 1st day of the following month and then add 3 months to the resut. e.g.: update courseRights set courseRightsLevelExpires = (LAST_DAY('yyyy-mm-dd') + 1 day) + 3 MONTHS – Bobok Perona Aug 19 '18 at 14:35
  • update courseRights set courseRightsLevelExpires = (LAST_DAY(courseRightsLevelExpires) + 1 DAY) + 3 MONTHS – Bobok Perona Aug 19 '18 at 14:42