CODEWITHSUNDEEP
sqlsql-server
Lakshin Karunaratne
Lakshin Karunaratne

Reputation: 455

How to change day of date in SQL server and set last day of month if the day does not exist in the month

I tried using this

dateadd(month, 0, dateadd(day,(30-datepart(dd,'2015-02-28')),'2015-02-28'))

to get the required output and instead of getting '2015-02-28' i get '2015-03-02'. How is it possible to change day of date in SQL and set last day of month if the day does not exist in the month ?

====Update with sample data =============

Note: Goal is not to get the last day of the month

If i want to change the day to 30 and if it's a month which has only 28 days. it should be the end of the month date. If not date should be 30th.

Changing the day to 30th If Feb it should be - '2015-02-28' If march it should be - '2015-03-30' If April it should be - '2015-04-30'

Upvotes: 2

Views: 2324

Answers (3)

David Dubois
David Dubois

Reputation: 3932

-- Replace Day portion of @OrigDate with @NewDay.
-- If the result would be beyond the end of the month, return the last day of the month

create function ReplaceDay ( @OrigDate date, @NewDay int ) 
returns date
as 
begin

declare @NewDate date

-- Deal with extreme cases, in case someone passes @NewDay = 7777 or @NewDay = -7777

if @NewDay < 1
  set @NewDay = 1

if @NewDay > 31 
  set @NewDay = 31

-- Subtract the DAY part of the original date from the new day.
-- Add that many days to the original date

-- Example:  if the original date is 2018-02-08 and you want to replace 08 with 17, then add 17-08=9 days

set @NewDate = DateAdd ( d, @NewDay-Day(@OrigDate), @OrigDate )

-- Have we ended up in the next month? 
-- If so subtract days so that we end up back in the original month.
-- The number of days to subtract is just the Day portion of the new date.

-- Example, if the result is 2018-03-02, then subtract 2 days

if Month(@NewDate)<>Month(@OrigDate)
  set @NewDate = DateAdd ( d, -Day(@NewDate), @NewDate )

return @NewDate

end

go

select dbo.ReplaceDay ( '2017-02-08', 17 ) -- Returns 2017-02-17 

select dbo.ReplaceDay ( '2017-02-08', 30 ) -- Returns 2017-02-28

Upvotes: 0

B3S
B3S

Reputation: 1051

for sql server 2012 or higher versions please check HoneyBadger's answer. for older versions:

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@mydate)+1,0))

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15140

There exists a function for this if your sql server version is 2012 or higher:

SELECT EOMONTH('2015-02-15')

returns 2015-02-28

Upvotes: 3

Related Questions