While reading ColdFusion blog of Ben Nadel Getting the Previous Day In ColdFusion, Excluding Saturday And Sunday, I realize that I use similar function in 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 a 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 the above function and provide the 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')
Let me know what you think of this blog post. I personally found this one of the most interesting functions related to daytime and worth bookmarking.
Reference : Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
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)
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.