Get yesterdays Date without weekends

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

General SQL Server Forums

New to SQL Server Programming

Get yesterdays Date without weekends

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2011-08-23 : 10:15:39

Hi need to create a report that excludes weekends. I was using this and it worked great Select GETDATE() -2, until Monday came along and I needed the data from last friday. I wanted to exclude the weekends.

How can I do this?

I saw something like this, but this doesn't help either.

Select DATEADD(d, - 1, { fn CURDATE() })

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 11:15:03

Try this. It will set up a conditional substraction depending on what "today" is:


SELECT	
DATEADD(DD, 
	CASE	WHEN DATEPART(DW, GETDATE())=2 THEN -2
	        WHEN DATEPART(DW, GETDATE()) BETWEEN 3 AND 6 THEN -1 ELSE 0 END,
	GETDATE())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-23 : 11:23:35

last suggestion depends on your servers date first setting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-23 : 11:55:19

Good point Visakh, the datepart dw would just be adjusted depending on the server's date first

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-08-23 : 12:10:47

This does not depend on any setting of datefirst or language:

select
	a.DT,
	DateWanted = dateadd(dd,datediff(dd,0,a.DT),0)+
	case datediff(dd,-53690,a.DT)%7
	when 0
	then -3
	when 6
	then -2
	else -1 end,
	DayOfWeek = datename(dw,a.DT)
from
	(-- Test Dates
	select DT = getdate()	union all
	select DT = getdate()+1	union all
	select DT = getdate()+2	union all
	select DT = getdate()+3	union all
	select DT = getdate()+4	union all
	select DT = getdate()+5	union all
	select DT = getdate()+6	union all
	select DT = getdate()+7
	) a
order by
	a.DT

Results:

DT                      DateWanted              DayOfWeek
----------------------- ----------------------- ----------
2011-08-23 11:07:22.217 2011-08-22 00:00:00.000 Tuesday
2011-08-24 11:07:22.217 2011-08-23 00:00:00.000 Wednesday
2011-08-25 11:07:22.217 2011-08-24 00:00:00.000 Thursday
2011-08-26 11:07:22.217 2011-08-25 00:00:00.000 Friday
2011-08-27 11:07:22.217 2011-08-26 00:00:00.000 Saturday
2011-08-28 11:07:22.217 2011-08-26 00:00:00.000 Sunday
2011-08-29 11:07:22.217 2011-08-26 00:00:00.000 Monday
2011-08-30 11:07:22.217 2011-08-29 00:00:00.000 Tuesday

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources