canteyhanger
asked on
SQL Date Function
Hi, I need help with a udf function in MSSQL, please. We have a program that we put a due date in a particular field and we have child events that generate from that date and are uploaded to Outlook through Exchange. What we are trying to do is make those child events never pop on a weekend. If the date falls on a Saturday it needs to push back to Thursday and the Sunday date would push back to Friday. We had someone write a script for us but we can't make it work. I am pasting it in here in hopes that someone may be able to help.
Thanks,
Tony.
-------------------------- ------
USE [Lawbase]
GO
/****** Object: UserDefinedFunction [dbo].[udf_WKDELIM] Script Date: 01/21/2013 12:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_WKDELIM] (@dtDate DATETIME, @strPrev VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
Set @strPrev = 'Previous'
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO
Thanks,
Tony.
--------------------------
USE [Lawbase]
GO
/****** Object: UserDefinedFunction [dbo].[udf_WKDELIM] Script Date: 01/21/2013 12:15:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_WKDELIM] (@dtDate DATETIME, @strPrev VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
Set @strPrev = 'Previous'
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO
ASKER
Thanks, I will try it and let you know how it works out.
This does seem to be working in that is does return the Friday date if that is passed to the script - I have stripped out the second variable as that seems to be the issue - the solution above will not work as Saturday will return Thursday as the last working day rather than Friday - the following code highlights that the script works - you just need to use the returned date without the variable as I don't know what it fulfils - following code will demonstrate
You will then get Friday for sat sunday or the current date if it is not weekend
Just set the getdate - number to show a weekend day such as -3 today will give you Saturday and you can see the effect.
CREATE FUNCTION [dbo].[udf_WKDELIM](@dtDate DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO
SELECT [dbo].[udf_WKDELIM](GETDATE()-1) AS DayOfWeek
You will then get Friday for sat sunday or the current date if it is not weekend
Just set the getdate - number to show a weekend day such as -3 today will give you Saturday and you can see the effect.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi mark_wills,
Just a quick question about the return dateadd(day,-2,@dtdate) clause - if it's Saturday and you apply a -2 wouldn't that give you Thursday rather than Friday? Should there not be a case of -1 for Saturday and -2 for Sunday?
Regards
Just a quick question about the return dateadd(day,-2,@dtdate) clause - if it's Saturday and you apply a -2 wouldn't that give you Thursday rather than Friday? Should there not be a case of -1 for Saturday and -2 for Sunday?
Regards
Sorry mark_wills just reread the requirement - thought a weekend was to be read as a Friday hence the case statement - I think the previous working day fro the original scriopt send me down the wrong road.
REgards
REgards
No worries :)
ASKER
First of all, thanks to everyone for the help. We are SQL neophites and can get into trouble trying to do this on our own!
Buttercup,
We tried yours and still got no dates to move back. Not sure what happened there.
Owenja,
Yours worked for Sunday but Saturday stayed on Saturday.
Mark_Wills,
Yours we have not tried yet but looks a lot simpler than the others. I can even understand it! I will let you know how it comes out as we try this solution.
You guys are great, all of you!
Buttercup,
We tried yours and still got no dates to move back. Not sure what happened there.
Owenja,
Yours worked for Sunday but Saturday stayed on Saturday.
Mark_Wills,
Yours we have not tried yet but looks a lot simpler than the others. I can even understand it! I will let you know how it comes out as we try this solution.
You guys are great, all of you!
How are you testing? These are the results I got:
SELECT DATENAME(dw,dbo.udf_WKDELIM2('jan 28, 2013')) AS MondayTest,
DATENAME(dw,dbo.udf_WKDELIM2('jan 29, 2013')) AS TuesdayTest,
DATENAME(dw,dbo.udf_WKDELIM2('jan 30, 2013')) AS WednesdayTest,
DATENAME(dw,dbo.udf_WKDELIM2('jan 31, 2013')) AS ThursdayTest,
DATENAME(dw,dbo.udf_WKDELIM2('feb 1, 2013')) AS FridayTest,
DATENAME(dw,dbo.udf_WKDELIM2('feb 2, 2013')) AS SaturdayTest,
DATENAME(dw,dbo.udf_WKDELIM2('feb 3, 2013')) AS SundayTest
ASKER
We are just adding the udf in a var. for a test case we set up. We created the case and created a workflow within and called the udf from in there. I guess we weren't smart enough to try it inside SQL! That would have been the easy way. I will get with my other compadre today and try it all again.
Thanks.
Thanks.
ASKER
To all, thank you for helping us with this. We got it to work as expected.
CREATE FUNCTION [dbo].[udf_WKDELIM2] (@dtDate DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @rtResult DATETIME
IF DATENAME(dw,@dtDate) = 'Saturday' OR DATENAME(dw,@dtDate) = 'Sunday'
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END