Determine Previous Sunday's Datetime

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

Transact-SQL (2000)

Determine Previous Sunday's Datetime

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-17 : 07:05:29

Hi, I need help please.

I have to filter on the Previous Sunday's Datetime.
So if i execute the qry on Tuesday the 18 March I want the value of the Sunday that has passed: 2008/03/16 00:00:00.000

Please Assist!

Regards

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-17 : 08:39:18

hi,

select DateAdd(Day,0,datediff(day,0,dateadd(day,1-datepart(dw, '03/17/2008'), getdate()))) ;

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-17 : 08:47:46

Thank You very much, i just need the time to be "00:00:00.000"

how do i convert the time to show just zero's?

Regards

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-17 : 08:55:26

Wow - this works!
select DateAdd(Day,0,datediff(day,0,dateadd(day,1-datepart(dw, '03/17/2008'), getdate()))) ;

I have one small problem, i'm trying to automate the entire qry.
So the qry could be executed anytime & it won't necessarily be "1-"
How do i automatically insert the date "'03/17/2008'" to another date & the amount of days back "1-"

Please Assist!

Thank You very much!

Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-17 : 09:01:05

Use getdate() in place of '03/17/2008'

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-17 : 09:14:35

Thank Very much - I really appreciate the assistance.
I would have been lost if not for all the Help & kindness.
Regards

SELECT
case when DATEPART(dw, GETDATE()) = 2
then DateAdd(Day,0,datediff(day,0,dateadd(day,1-datepart(dw, getdate()), getdate())))
when DATEPART(dw, GETDATE()) = 3
then DateAdd(Day,0,datediff(day,0,dateadd(day,2-datepart(dw, getdate()), getdate())))
when DATEPART(dw, GETDATE()) = 4
then DateAdd(Day,0,datediff(day,0,dateadd(day,3-datepart(dw, getdate()), getdate())))
when DATEPART(dw, GETDATE()) = 5
then DateAdd(Day,0,datediff(day,0,dateadd(day,4-datepart(dw, getdate()), getdate())))
when DATEPART(dw, GETDATE()) = 6
then DateAdd(Day,0,datediff(day,0,dateadd(day,5-datepart(dw, getdate()), getdate())))
when DATEPART(dw, GETDATE()) = 7
then DateAdd(Day,0,datediff(day,0,dateadd(day,6-datepart(dw, getdate()), getdate())))
end as WkStrDte

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-17 : 10:56:58

This will give you the Sunday on or before a given date, no matter what the day of the week is.

select Sun = dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684)

You can also use the function on this link:
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

CODO ERGO SUM

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-18 : 07:59:33

Great Stuff!!!

My previous Qry was not working today. It worked yesterday because it brought back Sunday's date but today it brought back yesterday's date!

select Sun = dateadd(dd,(datediff(dd,-53684,GETDATE())/7)*7,-53684)

Is there code for the other days of the week as well - would be nice to have for future purposes.

Thank You very much!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-18 : 09:05:02

Read this excellent article by Peso

http://www.sqlteam.com/article/datediff-function-demystified

-53684 days from 01/01/1900 is Sunday, -53683 is Monday, etc.

Jim

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-18 : 10:11:29

quote:
Originally posted by ismailc
...Is there code for the other days of the week as well - would be nice to have for future purposes....

Read the link I gave you on my previous post.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources