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://www.SQLAuthority.com)
pinaldave
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?