May 1, 2014 at 12:51 pm
Hi,
I would like to get the second last work day of the month. Can anyone help me with that.
May 1, 2014 at 1:13 pm
Is a workday Monday thru Friday? Do you need to be concerned with holidays?
May 1, 2014 at 1:30 pm
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/
May 1, 2014 at 1:47 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2014 at 2:40 am
Hi,
Thanks for the reply. Yes the workday runs through Monday to Friday
May 2, 2014 at 7:50 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2014 at 8:12 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 21, 2014 at 6:30 pm
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.
----------------------------------------------------
May 23, 2014 at 12:50 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply