CODEWITHSUNDEEP
sql-server
Benjo
Benjo

Reputation: 95

Add a number (13) to the month and change the day to the 1st of that month

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())

Upvotes: 0

Views: 165

Answers (3)

Ben Thul
Ben Thul

Reputation: 32697

SQL 2012+

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), 12))

Upvotes: 0

SlimsGhost
SlimsGhost

Reputation: 2909

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 
)

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

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

select dateadd(month, datediff(month, 0, dateadd(month, 13, GETDATE())), 0)

Upvotes: 2

Related Questions