DateAdd together with DateDiff

Question

Post reply

DateAdd together with DateDiff

tprocureur

SSC Veteran

Points: 278
More actions
December 7, 2009 at 1:31 am

#214552

Hi,
I would like to achieve the following:
I need an update query wich would do the following.
Suppose i have a deadline column in a table, with value '6/01/2010 0:00:00' (dd/mm/yyyy)
Today we are the '7/12/2009 0:00:00'.
When i run the update query today it should change the deadline value to '7/01/2010 0:00:00'...
But suppose i run the query on '8/12/2009 0:00:00' then the deadline value should change to '8/01/2010 0:00:00'
So i made this query:
SELECT DATEADD(DAY,DATEDIFF(day, '6/01/2010 0:00:00', getdate()),'6/01/2010 0:00:00')
But this makes the day change correctly but the month and year arent correct.
Cause the result of the above query is: 2009-12-07 00:00:00.000
But it should have been: 2010-01-07 00:00:00.000
How can i achieve my goal?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

If the deadline is going to be one month ahead of the current date when the update is happening, then can you use this?

sorry if I underestimated your requirement

Select dateadd(mm,1, dateadd(day,0, datediff(day,0,getdate())))

---------------------------------------------------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

What should happen to a deadline date of 28th February 2010 on 29th January 2010?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

Not sure but these date functions handle these things, no?

This,

Select dateadd(mm,1, dateadd(day,0, datediff(day,0,'2010-01-29')))

would give me the result,

2010-02-28 00:00:00.000

Edited: Ohh ignore it please Chris, I now got what you are asking for. Thanks.

---------------------------------------------------------------------------------

djyoungberg SSC Enthusiast Points: 100 More actions · Answer 4

Are you simply wanting to get the first day of the month?

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,'5/10/2012'),0)

This returns '5/1/2012'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Is this what you are looking for?

declare @ThisDate datetime;

set @ThisDate = '7/12/2009 0:00:00';

select dateadd(yy, 1, DATEADD(MM, DATEDIFF(mm, 0, @ThisDate), 0))

set @ThisDate = '8/12/2009 0:00:00';

select dateadd(yy, 1, DATEADD(MM, DATEDIFF(mm, 0, @ThisDate), 0))

set @ThisDate = GETDATE();

select dateadd(yy, 1, DATEADD(MM, DATEDIFF(mm, 0, @ThisDate), 0))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

It's been longer than a coffee break since the OP last posted anything 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden