Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Second Last work day of month Expand / Collapse
Author
Message
Posted Thursday, May 1, 2014 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 2:54 AM
Points: 2, Visits: 13
Hi,
I would like to get the second last work day of the month. Can anyone help me with that.
Post #1566828
Posted Thursday, May 1, 2014 1:13 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:23 PM
Points: 157, Visits: 981
Is a workday Monday thru Friday? Do you need to be concerned with holidays?
Post #1566836
Posted Thursday, May 1, 2014 1:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 1,072, Visits: 3,333
See this link.
http://www.sqlservercentral.com/scripts/Date/68389/

A calendar table will probably work well for this use.


Michael L John
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Post #1566841
Posted Thursday, May 1, 2014 1:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
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)
Post #1566844
Posted Friday, May 2, 2014 2:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 2, 2014 2:54 AM
Points: 2, Visits: 13
Hi,
Thanks for the reply. Yes the workday runs through Monday to Friday
Post #1566957
Posted Friday, May 2, 2014 7:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,325, Visits: 12,811
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)
Post #1567034
Posted Friday, May 2, 2014 8:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 2,328, Visits: 3,505

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1567046
Posted Wednesday, May 21, 2014 6:30 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:13 PM
Points: 456, Visits: 1,060
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.
Post #1573383
Posted Friday, May 23, 2014 12:50 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
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

Post #1573896
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse