date calculation problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

date calculation problem

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-03-02 : 10:22:04

i'm running a sp every tuesday and need to find the date of the previous sunday. so if i run it today it should return 2/29/04. but what i'm using is returning 2/27/04. here it is:
DATEADD(dd, DATEPART(dw, GETDATE()) * - 2 + 2, GETDATE())

what's wrong? what should i use so the correct date is returned every tuesday?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 10:23:54

select DATEADD(dd, DATEPART(dw, GETDATE()) * - 1 + 1, GETDATE())

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-03-02 : 10:27:04

will this work next week though?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 10:35:16

Yes - it subtracts the weekday number (in this case 3) + 1 (to give 2) to the current date.
i.e. on tuesday it will subtract 2 from the date to give the sunday.
It will work for any day of the week not just tuesday (for which you could hard code -2).

Why not test it for yourself

set nocount on
declare @d datetime
select @d = getdate()
while @d < '20040330'
begin
select @d, datename(dw, @d), DATEADD(dd, DATEPART(dw, @d) * - 1 + 1, @d)
select @d = @d + 1
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-03-02 : 10:38:36

great, i see now. thx for the help. also, how do i return just the date and not the time? sorry, i'm a beginner when it comes to dates

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-02 : 10:41:25

convert(datetime,convert(varchar(8),getdate(),112))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources