Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateAdd together with DateDiff


DateAdd together with DateDiff

Author
Message
tprocureur
tprocureur
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 3
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?
Nabha
Nabha
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 1814
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
ChrisM@Work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10144 Visits: 19211
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
Exploring Recursive CTEs by Example Dwain Camps
Nabha
Nabha
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 1814
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
djyoungberg
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 156
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
Lynn Pettis
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26394 Visits: 38120
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))




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@Work
ChrisM@Work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10144 Visits: 19211
It's been longer than a coffee break since the OP last posted anything :-D

“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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search