sql dates - 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 2005 Forums

Transact-SQL (2005)

sql dates

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-19 : 06:59:13

Hi,

I have the following issue, i need to do a week on week comparison of data. But the data we gain from the 3rd party only presents the time on a 'weekenddate' so it is always a sunday

For example last 2 dates i have:
11-10-2009
04-10-2009

They are all sundays, so when I do in my query

WHERE (DATEDIFF(week, WeekEndDate, GETDATE()) = 1)

This doesnt give me last week which the sunday was yesterday (18-10-2009) but gives me the week before that so it looks at (11-10-2009)

Any ideas on how to resolve this?

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-10-19 : 07:05:44

Have a look in BOL for DATEFIRST.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-19 : 07:13:35

select getdate()-datepart(weekday,getdate())+1

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-19 : 07:37:24

[code]WHERE DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, -1)[/code]

N 56°04'39.26"
E 12°55'05.63"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-19 : 08:50:03

I got it to work somehow with

WHERE (DATEDIFF(week, WeekEndDate - 1, GETDATE()) = 1)

But thanks for all the replies

quote:
Originally posted by Peso

WHERE DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, -1)

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-19 : 10:34:03

All operations with WEEK and WEEKDAY are dependant on server settings.
So you have to be careful if you run the scripts on another server.

You managed to get this working [by accident] because SQL Server as default treats a week as starting with sunday and ending with saturday.
That's why the "-1" trick got your query working. Substracting one day from sunday gives saturday, which incidentally is last day of a default sql server week.

N 56°04'39.26"
E 12°55'05.63"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-10-19 : 10:42:20

Thank you very much peso, i just picked up something new today!

quote:
Originally posted by Peso

All operations with WEEK and WEEKDAY are dependant on server settings.
So you have to be careful if you run the scripts on another server.

You managed to get this working [by accident] because SQL Server as default treats a week as starting with sunday and ending with saturday.
That's why the "-1" trick got your query working. Substracting one day from sunday gives saturday, which incidentally is last day of a default sql server week.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-19 : 10:50:56

You're welcome.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources