Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Second Last work day of month


Second Last work day of month

Author
Message
praveen.arokiam
praveen.arokiam
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 13
Hi,
I would like to get the second last work day of the month. Can anyone help me with that.
Bill Talada
Bill Talada
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 1817
Is a workday Monday thru Friday? Do you need to be concerned with holidays?
Michael L John
Michael L John
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2054 Visits: 7401
See this link.
http://www.sqlservercentral.com/scripts/Date/68389/

A calendar table will probably work well for this use.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
Or if you don't need the flexibility of a calendar table (which is probably what I would use) and you don't care about holidays you could do some simple date math. This will return the last 7 days of any given month.


declare @ThisDate datetime = getdate()

select DATEADD(day, -DayNum, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) as MyDay
from
( Values(1),(2),(3),(4),(5),(6),(7)) d(DayNum)
order by DayNum desc



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
praveen.arokiam
praveen.arokiam
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 13
Hi,
Thanks for the reply. Yes the workday runs through Monday to Friday
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16632 Visits: 17024
praveen.arokiam (5/2/2014)
Hi,
Thanks for the reply. Yes the workday runs through Monday to Friday


So a minor tweak to what I posted will get you the last Friday of any given month.


declare @ThisDate datetime = getdate();

with LastSeven as
(
select DATEADD(day, -DayNum, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) as MyDay
from
( Values(1),(2),(3),(4),(5),(6),(7)) d(DayNum)
)
select *
from LastSeven
where DATENAME(weekday, MyDay) = 'Friday'



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3950 Visits: 6686


DECLARE @date_with_month datetime
SET @date_with_month = GETDATE()

;WITH
cteDays AS (
SELECT 1 AS day# UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 7 UNION ALL
SELECT 7
)
SELECT TOP (1) day_of_month
FROM (
SELECT TOP (2) day_of_month
FROM cteDays
CROSS APPLY (
SELECT DATEADD(DAY, -day#, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date_with_month) + 1, 0)) AS day_of_month
) AS assign_column_name_to_day_of_month_calc
WHERE
DATEDIFF(DAY, 0, day_of_month) % 7 <= 4
ORDER BY
day_of_month DESC
) AS derived
ORDER BY day_of_month ASC




SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
MMartin1
MMartin1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1120 Visits: 2015
For the current month ...


WITH MYCTE AS (
SELECT row_number() over (order by [dates] DESC ) as dayNum, [dates], datename(weekday, dates) as [Day Name]
FROM
( /* the last seven days in the month*/
SELECT dateadd(dd, ROW_NUMBER() OVER( ORDER BY (SELECT 'a')) -1, dateadd(m,1,dateadd(d,-day(getdate())+1, dateadd(d, 0, datediff(d, 0 , getdate()))))-7) as [Dates]
from (VALUES(1),(2),(3),(3),(4),(5),(6))L(s)
) as v
WHERE datename(weekday, dates) not in ('saturday','sunday')
)
SELECT * from MYCTE where dayNum = 2




You can substitute getdate() with a datetime or date variable and make it any day in the month you wish to analyse.

----------------------------------------------------
How to post forum questions to get the best help
Sachin Nandanwar
Sachin Nandanwar
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 2633
Since you have posted the question in SQL Server 2012 TSQL category I assume you are using SQL Server 2012.If that's the case then you can simply use the EOMONTH function

select  case datename(WEEKDAY,EOMONTH(getdate())) 
when 'Saturday' then convert(datetime,(EOMONTH(getdate())))-2
when 'Sunday' then convert(datetime, (EOMONTH(getdate())))-3
else convert(datetime,EOMONTH(getdate()))-1 end



--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search