Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DateAdd together with DateDiff Expand / Collapse
Author
Message
Posted Monday, December 07, 2009 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 14, 2010 4:10 AM
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?
Post #829692
Posted Monday, December 07, 2009 1:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 3:36 AM
Points: 579, Visits: 1,803
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())))



---------------------------------------------------------------------------------
Post #829694
Posted Monday, December 07, 2009 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Post #829729
Posted Monday, December 07, 2009 4:30 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, August 02, 2011 3:36 AM
Points: 579, Visits: 1,803
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.


---------------------------------------------------------------------------------
Post #829734
Posted Thursday, May 10, 2012 7:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 9:45 AM
Points: 6, Visits: 140
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'
Post #1298306
Posted Thursday, May 10, 2012 8:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 22,492, Visits: 30,186
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))





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)
Post #1298309
Posted Friday, May 11, 2012 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:33 AM
Points: 6,754, Visits: 12,854
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1298372
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse