Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » DateAdd together with DateDiff
Add to briefcase

DateAdd together with DateDiff Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
tprocureur
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
Nabha
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
ChrisM@Work
Posted Monday, December 07, 2009 3:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
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
Nabha
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
djyoungberg
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: Friday, June 07, 2013 1:14 PM
Points: 6, Visits: 121
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
Lynn Pettis
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 @ 4:03 PM
Points: 21,832, Visits: 27,859
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
ChrisM@Work
Posted Friday, May 11, 2012 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.