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

LikeLike

Try it:

select DATENAME(dw ,convert(datetime, substring(eventstartdttm, 0,9),3))

— 3 for format dd/MM/yy

Good-Luck!

LikeLike

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

LikeLike

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

LikeLike

@RAHUL

You mean this ??

select datename(dd,getdate()+8)

Result: 25

select datename (dw,getdate() +8)

Result : Friday –(if today is July 17, Thursday)

LikeLike

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?

LikeLike

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

LikeLike

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)

LikeLike

Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

Simple and good…!!!

Works well for me…

LikeLike

select DATEDIFF ( day , ’02/13/2009′ , ’02/12/2009′ )

select DATEDIFF ( hour , ’02/13/2009′ , ’02/12/2009′ )

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

it’s usefull..

thanks for your code..

:D

LikeLike

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.

LikeLike

Pingback: SQL SERVER – Weekly Series – Memory Lane – #039 | Journey to SQL Authority with Pinal Dave