CODEWITHSUNDEEP
sqlsql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Modify Date without DateAdd or DateDiff

Is it possible to modify the day part of a date without having to use DateAdd or having to calculate the date difference?

SET @ReturnDate = '5/16/2012'

SET @ModifyDay = 20

Modify only 16 to 20

Result should be 5/20/2012

The reason for this is that I am translating vb code into a stored proc:

    Dim InvDate_Day_of_Month As Integer = CInt(Day(InvDate))
    PaymentDate = DateAdd(DateInterval.Month, 1, InvDate)
    PaymentDate = New Date(PaymentDate.Year, PaymentDate.Month, DayofTheMonth)

Upvotes: 0

Views: 3652

Answers (2)

Seth Flowers
Seth Flowers

Reputation: 9190

If you are just trying to add 4 days to the date, you can literally just add 4 to it:

DECLARE @ReturnDate DATETIME;
SET @ReturnDate = '5/16/2012'

SELECT @ReturnDate, @ReturnDate + 4

This selects 2012-05-16 00:00:00.000 and 2012-05-20 00:00:00.000

Upvotes: 1

zimdanen
zimdanen

Reputation: 5626

SET @ReturnDate = @ReturnDate + (@ModifyDay - DATEPART(day, @ReturnDate))

Upvotes: 2

Related Questions