Calculating previous thursday date

please can someone help me.
I am trying to work out the previous thursday date, for example, todays
date is 31/03/2006 so the previous Thursday is 30/03/2006, again same
for 05/04/2006, previous date is 30/03/2006 but if the date is
06/04/2006 then the previous date is 06/04/2006.
This must work in a View.
I am using SQL Server 2000.
Cheers.
paulLook up DATEADD() in Books On Line and subtract 7 days.|||--CELKO-- wrote:
> Look up DATEADD() in Books On Line and subtract 7 days.
But there's more to it than that. I think I've had a brain-fart. The
best statement I can come up with is:
DATEADD(day,(0 - (((DATEPART(wday,CURRENT_TIMESTAMP) + 1) % 7) + 1))
% -7,CURRENT_TIMESTAMP)
but surely there's something shorter?
Damien|||First you check wday using DATENAME function.
And can use CASE WHEN statement.
if the date is 'thursday' , you can make previous thursday using DATEADD
function .
"PP"?? ??? ??:

> please can someone help me.
> I am trying to work out the previous thursday date, for example, todays
> date is 31/03/2006 so the previous Thursday is 30/03/2006, again same
> for 05/04/2006, previous date is 30/03/2006 but if the date is
> 06/04/2006 then the previous date is 06/04/2006.
> This must work in a View.
> I am using SQL Server 2000.
> Cheers.
> paul
>|||On 30 Mar 2006 18:31:39 -0800, PP wrote:

>please can someone help me.
>I am trying to work out the previous thursday date, for example, todays
>date is 31/03/2006 so the previous Thursday is 30/03/2006, again same
>for 05/04/2006, previous date is 30/03/2006 but if the date is
>06/04/2006 then the previous date is 06/04/2006.
>This must work in a View.
Hi Paul,
Here's a trick that doesn't depend on the SET DATEFIRST setting:
CREATE VIEW LastThursday
AS
SELECT DATEADD(day,
DATEDIFF(day, '19000104', CURRENT_TIMESTAMP) / 7 * 7,
'19000104') AS LastThusrday
go
SELECT * FROM LastThursday
go
DROP VIEW LastThursday
go
Hugo Kornelis, SQL Server MVP