August 15, 2009 at 6:52 pm
I need a function that will return a date "date only, Not date time" the date needs to be today() - 2 business days and we would like to have a table that holds holidays.
any help would be great
August 15, 2009 at 8:48 pm
Here's a stored procedure that demonstrates how to do this:
CREATE PROC spWorkDays_Count as
BEGIN TRY DROP TABLE #WORKDAYS END TRY BEGIN CATCH END CATCH;
CREATE TABLE #WORKDAYS(
DayNo INT
, DateOnly DATETIME NOT NULL PRIMARY KEY
, IsWorkDay SMALLINT
);
WITH cte10kNumbers AS
(
SELECT TOP 10000
ROW_NUMBER() OVER(ORDER BY id) AS N
FROM master..syscolumns
ORDER BY id, number
)
, cteDigits AS
(
SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
)
, cte100kNumbers as
(
SELECT d*10000 + N AS N
FROM cte10kNumbers, cteDigits
)
, cte2009Days AS
(
SELECT
N AS DayNo
, DATEADD(DAY,N,0) AS Dat
FROM cte100kNumbers
WHERE N >= DATEDIFF(DAY,0, '20090101')--YYYYMMDD is a universal date format
AND N <= DATEDIFF(DAY, 0, '20091231')
)
, cte2009WorkingDays AS
(
SELECT DayNo, Dat AS DateOnly
,DATENAME(DAY, Dat) AS WKDAY
, CASE WHEN DATENAME(WEEKDAY, Dat) IN('SUNDAY','SATURDAY') THEN 0
WHEN RIGHT(CONVERT(VARCHAR(10), Dat, 121), 5) IN('01-01','07-04','12-25') THEN 0
-- add year-specific holidays in here also
ELSE 1 END AS IsWorkDay
FROM cte2009Days
)
INSERT INTO #WORKDAYS
SELECT DayNo, DateOnly, IsWorkDay
FROM cte2009WorkingDays;
SELECT DateOnly
FROM #WORKDAYS d
WHERE DateOnly BETWEEN GETDATE()-7 AND GETDATE()
AND 2 = (
SELECT SUM( IsWorkDay )
FROM #WORKDAYS d2
WHERE d2.DateOnly BETWEEN GETDATE()-7 AND GETDATE()
AND d.DateOnly < d2.DateOnly
)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 17, 2009 at 7:14 am
Here's a good blog post[/url] about setting US Holidays in a calendar table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 17, 2009 at 4:57 pm
If you include weekends with holidays in your holiday table, the following query works.
-- New year's Day, 2009 example
declare @startDate datetime
set @startDate = '1/5/2009' -- Monday
declare @backupDays int
set @backupDays = 2
-- this should be a permanent table, indexed on the date column
declare @NonBiz table (nonbizDay datetime)
insert into @nonbiz
select '1/1/2009' union all -- New Year's Day
select '1/3/2009' union all-- Saturday
select '1/4/2009'-- Sunday
-- everything above is just setup... one query solves it
;with tally (N) as
(select top 1100 row_number() over(order by id) from syscolumns) -- three years back is plenty for this
,testDates (result) as
(select dateadd(dd, -N, @startdate) from tally)
,bizDates (N,result) as
(select row_number() over(order by result desc),result
from testDates t
left join @NonBiz n on t.result = n.Nonbizday
where n.NonBizDay is null)
select result from bizDates
where N = @backupDays
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply