Yesterdays date in a business week

[Wayne79]

I'm looking for a way to easily tell yesterdays date in a business week. I can get Tue through Friday easily enough but I can seem to get Mondays yesterday (Friday).

Thanks in advance for any assistance.

 

[gmmastros]

I hesitate to answer this because there are various factors involved here. Specifically, what is considered a business day, and more importantly, what is the first day of the week.

This code snippet assumes that your business week is Monday through friday.

Declare @TheDate DateTime
Set @TheDate = '2007-02-19'

Set DateFirst 7

select Case When DatePart(weekday, @TheDate) = 2 
            Then @TheDate - 3
            Else @TheDate - 1
            End As Yesterday

Copy/Paste this to query analyzer. Play around with the Set @TheDate = 'SomeDate' line to make sure this does what you expect.

Also, look up the Set DateFirst command in books on line.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

There is a 'slight' glitch with the code I provided.

If the date you are testing is a Monday, it will return the previous Friday, otherwise, it will return the previous day. I suspect that if the day you are testing happens to be a sunday, then you would also want to return the previous friday. And of course, of the day you are testing happens to be a Saturday, then return the previous Friday. By changing the code slightly, you can accomodate the weekends. The change would look like this...

Declare @TheDate DateTime
Set @TheDate = '2007-02-12'

Set DateFirst 7

select Case When DatePart(weekday, @TheDate) = 1 Then @TheDate - 2
            When DatePart(weekday, @TheDate) = 2 Then @TheDate - 3
            Else @TheDate - 1
            End As Yesterday

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[AlexCuse]

I believe that there is a command you need to set to be certain that the first day of the week is what you think it is. But this is what I came up with (assumes business days are m-f and your day 1 is sunday)

--test data
declare @datetest table (dt datetime)

insert into @datetest
select '2007-02-18'
union all select '2007-02-19'
union all select '2007-02-20'
union all select '2007-02-21'
union all select '2007-02-22'
union all select '2007-02-23'
union all select '2007-02-24'

--here's the query
select dt, datepart(dw, dt)
, case datepart(dw,dt)
when 2 then dateadd(day, -3, dt)
when 1 then dateadd(day, -2, dt)
else dateadd(day, -1, dt) end as PreBDay
from @datetest

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Wow that was way late. This is what I get for not having the internet on my computer that has SQL Server on it I suppose.

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

>> I believe that there is a command you need to set to be certain that the first day of the week is what you think it is.

Set DateFirst

For us americans, it's usually...

Set DateFirst 7

You can see what your 'datefirst' is set to by running this...
Select @@DateFirst


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom