0

I am sure this one is simple but I am having a difficult time figuring it out. I am trying to add 13 months to a date and that resulting month needs to default to the 1st day of the month.

Example:
Date: 1/25/2016
Query results: 2/1/2017

Here is the query I am using:

SELECT Dateadd(month,13,getdate())
Hiten004
  • 2,425
  • 1
  • 22
  • 34
Benjo
  • 85
  • 2
  • 9
  • 1
    here is an example of how to get the first day of month.. http://stackoverflow.com/questions/1520789/how-can-i-select-the-first-day-of-a-month-in-sql – JamieD77 Mar 23 '16 at 18:21

3 Answers3

2

This should work for you. Just replace GETDATE() with your date.

select dateadd(month, datediff(month, 0, dateadd(month, 13, GETDATE())), 0)
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
0

You can just construct the date from the constituent parts, by adding 1 year + 1 month and forcing the day part to 1 (and taking care of the special case of month = 12), like this:

select DATEFROMPARTS (
    year(getdate()) + case when month(getdate()) = 12 then 2 else 1 end, 
    case when month(getdate()) = 12 then 1 else month(getdate()) + 1 end, 
    1 
)
SlimsGhost
  • 2,849
  • 1
  • 10
  • 16
0

SQL 2012+

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), 12))
Ben Thul
  • 31,080
  • 4
  • 45
  • 68