last business day of prior month

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

last business day of prior month

Author

Topic

DanielS
Starting Member

32 Posts

Posted - 2013-07-04 : 19:41:33

Hi, am looking for a piece of code to get the last business day of the prior month. I've seen plenty of codes for getting the last day of the prior month, but need it adjusted to be a weekday. I don't want to refer to another table as some have suggested, ie a holiday table, I'm not interested in excluding holidays, just excluding weekends.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-04 : 21:35:12

[CODE]

DECLARE @mydate DATE = '2013-03-04';

SELECT CASE DATEDIFF(dd, 0, EOMONTH(DATEADD(mm, -1, @mydate)))%7
WHEN 5 THEN DATEADD(dd, -1, (EOMONTH(DATEADD(mm, -1, @mydate)))) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, (EOMONTH(DATEADD(mm, -1, @mydate)))) -- if Sunday subtract two days
ELSE EOMONTH(DATEADD(mm, -1, @mydate)) END;
[/CODE]

DanielS
Starting Member

32 Posts

Posted - 2013-07-04 : 22:20:14

thank you

quote:
Originally posted by MuMu88

[CODE]

DECLARE @mydate DATE = '2013-03-04';

SELECT CASE DATEDIFF(dd, 0, EOMONTH(DATEADD(mm, -1, @mydate)))%7
WHEN 5 THEN DATEADD(dd, -1, (EOMONTH(DATEADD(mm, -1, @mydate)))) -- if Saturday subtract one day
WHEN 6 THEN DATEADD(dd, -2, (EOMONTH(DATEADD(mm, -1, @mydate)))) -- if Sunday subtract two days
ELSE EOMONTH(DATEADD(mm, -1, @mydate)) END;
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-05 : 01:31:10

Just a word of caution that EOMONTH works only in SQL 2012 version. So if you're using an earlier version use this small modification


DECLARE @mydate DATE = '2013-03-04';


SELECT CASE DATEDIFF(dd,0,DATEADD(mm,DATEDIFF(mm, 0, @mydate),-1))%7
		WHEN 5 THEN DATEADD(dd, -1, DATEADD(mm,DATEDIFF(mm, 0, @mydate),-1)) -- if Saturday subtract one day
		WHEN 6 THEN DATEADD(dd, -2, DATEADD(mm,DATEDIFF(mm, 0, @mydate),-1)) -- if Sunday subtract two days
		ELSE DATEADD(mm,DATEDIFF(mm, 0, @mydate),-1) 
       END;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources