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.