While reading ColdFusion blog of Ben Nadel Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday, I realize that I use similar function on my SQL Server Database. This function excludes the Weekends (Saturday and Sunday), and it gets previous as well as next work day.
Script to create function to get previous and next working day.
CREATE FUNCTION dbo.udf_GetPrevNextWorkDay (@dtDate DATETIME, @strPrevNext VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
IF @strPrevNext = 'Previous'
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
ELSE
SET @rtResult = DATEADD(d,-1,@dtDate)
--To find Next working day
ELSE
IF @strPrevNext = 'Next'
IF @intDay = 6
SET @rtResult = DATEADD(d,3,@dtDate)
ELSE
IF @intDay = 7
SET @rtResult = DATEADD(d,2,@dtDate)
ELSE
SET @rtResult = DATEADD(d,1,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO
Following examples will execute above function and provide desired results.
SELECT dbo.udf_GetPrevNextWorkDay('1/1/2007','Previous')
SELECT dbo.udf_GetPrevNextWorkDay('1/1/2007','Next')
SELECT dbo.udf_GetPrevNextWorkDay('12/31/2007','Previous')
SELECT dbo.udf_GetPrevNextWorkDay('12/31/2007','Next')
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Hi Pianl,
Hello, how are you? I need help with a sql query that I am trying to write. I am trying to convert a date into a day in a query where a field called eventstartdttm is a varchar field that holds date and time
for example if substring(eventstartdttm, 0,9) returns 30th Jan 2008 then I need it to say Wednesday. Is there a function that can help me do that? Thanks
Warm Regards,
Chitra
Try it:
select DATENAME(dw ,convert(datetime, substring(eventstartdttm, 0,9),3))
– 3 for format dd/MM/yy
Good-Luck!
I am a newbie in SQL
I am trying to structure a query wherein
I need to display data where the Qtrly end dates(3/31,6/30/9/30/12/31) have been moved for vendors.
also The Table has multiple records for the same vedor ending in 3/31 and I need to take a max
When I tried using date diff between Quarters for different Years it does not work.. any help here is appreciated
Hi,
I am looking for a function through which I can calculate a particular working day. let say I want to know the 8th working day from todays date.
Any help??? its urgent..
Thanks in advance.
Rahul
@RAHUL
You mean this ??
select datename(dd,getdate()+8)
Result: 25
select datename (dw,getdate() +8)
Result : Friday –(if today is July 17, Thursday)
This is a great function and I really like how this function works . Is there any way to account for Holidays? I have seen something where one could enter Holidays in a table (eg – tblHolidays –>brilliant naming huh?). When the workday falls on the date in the table, the function would then go onto the next working day.
How would I modify this Function to do this?
Just came across the need to do this myself. I made the function just call itself again in the event that the previous/next date falls on a holiday.
IF EXISTS(SELECT * FROM tblHolidays WHERE HolidayDate = @EndDate)
BEGIN
SELECT @EndDate = dbo.udf_GetPrevNextWorkDay(@EndDate, @strPrevNext)
END
hi Pianl,
I need a help from u.
I am having a field name issueDate datatype as smalldatetime.
I need this field and also the previous date
ex: Item id ItemName IssueDate
1023 XXXX 12/02/2009
1023 XXXX 13/02/2009
I need the answer is diff between the 13/02/2009(LastDate) – 12/02/2009(previous date)
[...] SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday [...]
Simple and good…!!!
Works well for me…
select DATEDIFF ( day , ’02/13/2009′ , ’02/12/2009′ )
select DATEDIFF ( hour , ’02/13/2009′ , ’02/12/2009′ )
how can i get the balance which the only given is the breakdown of payments, transaction amount and transaction date and i will use the transaction date as parameters.
for example(table form): this shoul be the output:
customer trans_amt payments trans_date balance
robert 200 10 1-1-09 190
robert 200 50 1-5-09 140
robert 200 60 1-7-09 80
i want to get balance for every trans_date
please help me
thanks
robert
how can i get the balance which the only given is the breakdown of payments, transaction amount and transaction date and i will use the transaction date as parameters.
for example(table form): this shoul be the output:
customer—-trans_amt—-payments—trans_date—-balance
robert——–200————–10———-1-1-09———-190
robert——–200————–50———-1-5-09———-140
robert——–200————–60———-1-7-09———-80
i want to get balance for every trans_date
please help me
thanks
robert
Built off this script to create the following udf that has option to exclude holidays and also to retrieve more than just 1 day prev/next:
CREATE FUNCTION dbo.udfGetPrevNextWorkDay
(
@StartDate DATETIME,
@bGetPrevious BIT = 0,
@bExcludeHolidays BIT = 1,
@MultipleDayCount INT = 0
)
RETURNS DATETIME
AS
BEGIN
DECLARE @EndDate DATETIME, @Day NVARCHAR(10), @DayCount INT
SET @Day = DATENAME(weekday, @StartDate)
IF @MultipleDayCount > 0
BEGIN
DECLARE @MultipleDayIndex INT, @MultipleDayDate DATETIME
SET @MultipleDayDate = @StartDate
SET @MultipleDayIndex = 1
WHILE @MultipleDayIndex < @MultipleDayCount
BEGIN
SELECT @MultipleDayDate = dbo.udfGetPrevNextWorkDay(@MultipleDayDate, @bGetPrevious, @bExcludeHolidays, 0)
SET @MultipleDayIndex = @MultipleDayIndex + 1
END
RETURN @MultipleDayDate
END
–To find Previous working day
IF @bGetPrevious = 1
BEGIN
SELECT @DayCount =
CASE @Day WHEN 'Sunday' THEN -2
WHEN 'Monday' THEN -3
ELSE -1
END
END
–To find Next working day
IF @bGetPrevious = 0
BEGIN
SELECT @DayCount =
CASE @Day WHEN 'Friday' THEN 3
WHEN 'Saturday' THEN 2
ELSE 1
END
END
SET @EndDate = DATEADD(d,@DayCount,@StartDate)
IF (@bExcludeHolidays = 1) AND (EXISTS(SELECT * FROM tblzcdIndexHoliday WHERE IndexHolidayCelebrationDate = @EndDate))
BEGIN
SELECT @EndDate = dbo.udfGetPrevNextWorkDay(@EndDate, @bGetPrevious, @bExcludeHolidays, 0)
END
RETURN ISNULL(@EndDate, @StartDate)
END
Script to calculate hollidays (i.e not using a table… Tricky part is Easter :)
– Drop function if exists
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID(N’[dbo].[IsHolliday]‘) AND xtype IN (N’FN’, N’IF’, N’TF’)) DROP FUNCTION [dbo].[IsHolliday]
GO
– Create function
CREATE FUNCTION dbo.IsHolliday
– +————————————————————————–+
– | Function : IsHolliday |
– | Author : Martin Grape |
– | Version : 1.1 |
– | Description : Returns 1 for holliday and 0 for all other days |
– | Parameters : @IsHolliday – Date to check |
– | Returns : 0 (workday) or 1 (holliday) |
– | Other : This is calculation for Swedish hollidays |
– | Example : SELECT dbo.IsHolliday(’2010-04-04′) –> 1 |
– +————————————————————————–+
(
@IsHolliday DATETIME
)
RETURNS INT
AS
BEGIN
— Declare variables
DECLARE @IsHollidayResult INT
DECLARE @IsHollidayYear INT
DECLARE @IsHollidayMonth INT
DECLARE @IsHollidayDay INT
DECLARE @IsHollidayMonthDay VARCHAR(5)
DECLARE @IsHollidayEasterDate DATETIME
— Declare variables
SET @IsHollidayYear = YEAR(@IsHolliday)
SET @IsHollidayMonth = MONTH(@IsHolliday)
SET @IsHollidayDay = DAY(@IsHolliday)
SET @IsHollidayMonthDay = RIGHT(CONVERT(CHAR(10), @IsHolliday, 120), 5)
— Set starting values
SET @IsHollidayResult = 0
— Check for set dates (this is for Sweden)
SET @IsHollidayResult = CASE WHEN @IsHollidayMonthDay IN (’01-01′, ’01-06′, ’05-01′, ’12-25′, ’12-26′) THEN 1 ELSE @IsHollidayResult END
SET @IsHollidayResult = CASE WHEN @IsHollidayResult = 0 AND @IsHollidayMonthDay = ’06-06′ AND @IsHollidayYear > 2004 THEN 1 ELSE @IsHollidayResult END
— Return if a set date
IF @IsHollidayResult = 1 BEGIN
RETURN @IsHollidayResult
END
— Calculate easter date
SET @IsHollidayEasterDate = CONVERT(DATETIME, CONVERT(CHAR(4), @IsHollidayYear) + ‘-’ +
RIGHT(’00′ + CONVERT(VARCHAR, CONVERT(INT, ((((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7)) – 7 * (CONVERT(INT, ((@IsHollidayYear % 19) + 11 * (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + 22 * (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7))) / 451)) + 114) / 31)), 2) + ‘-’ +
RIGHT(’00′ + CONVERT(VARCHAR, (((((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7)) – 7 * (CONVERT(INT, ((@IsHollidayYear % 19) + 11 * (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) + 22 * (((32 + 2 * ((CONVERT(INT, @IsHollidayYear / 100)) % 4) + 2 * (CONVERT(INT, (@IsHollidayYear % 100) / 4)) – (((19 * (@IsHollidayYear % 19) + (CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) / 4)) – (CONVERT(INT, ((CONVERT(INT, @IsHollidayYear / 100)) – (CONVERT(INT, (CONVERT(INT, @IsHollidayYear / 100)) + 8) / 25) + 1) / 3)) + 15) % 30)) – ((@IsHollidayYear % 100) % 4)) % 7))) / 451)) + 114) % 31) + 1), 2))
— Easter
IF @IsHolliday = @IsHollidayEasterDate – 2 OR
@IsHolliday = @IsHollidayEasterDate OR
@IsHolliday = @IsHollidayEasterDate + 1 BEGIN
RETURN 1
END
— Ascension Day
IF @IsHolliday = DATEADD(dd, 39, @IsHollidayEasterDate) BEGIN
RETURN 1
END
— Whitsun
IF @IsHolliday = DATEADD(dd, 49, @IsHollidayEasterDate) OR
(@IsHolliday = DATEADD(dd, 50, @IsHollidayEasterDate) AND @IsHollidayYear < 2005) BEGIN
RETURN 1
END
— Midsummer
IF @IsHolliday = DATEADD(dd, 7 – DATEPART(dw, CONVERT(CHAR(4), @IsHollidayYear) + '-06-20'), CONVERT(CHAR(4), @IsHollidayYear) + '-06-20') BEGIN
RETURN 1
END
— Halloween
IF @IsHolliday = DATEADD(dd, 7 – DATEPART(dw, CONVERT(CHAR(4), @IsHollidayYear) + '-10-31'), CONVERT(CHAR(4), @IsHollidayYear) + '-10-31') BEGIN
RETURN 1
END
RETURN 0
END
GO
it’s usefull..
thanks for your code..
:D
Hi,
I need help in A stored procedure.
I have to calculate a parameter only on week days,
For eg if i give input date to be 10th october(Sunday) and today is 28th october.
I have to calculate these parameters for 5 weekdays..
So if i give A date which is a sunday then it should automatically skip that day and should calculate from Monday to friday.
Another eg. Suppose the input date is a wednesday, then calculate the parameters for wednesday, thursday, friday, moday and tuesday.
Skipping the Saturday and sunday.
Could you please suggest a way out of this.