Solved

SQL Date Function

Posted on 2013-01-30
11
361 Views
Last Modified: 2013-02-01
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
0
Comment
Question by:canteyhanger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 11

Expert Comment

by:Simone B
ID: 38837183
Try this:

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
0
 

Author Comment

by:canteyhanger
ID: 38837271
Thanks, I will try it and let you know how it works out.
0
 

Expert Comment

by:owenja
ID: 38837334
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
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

Open in new window


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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 38839201
Mine is pretty much as per buttercup1...

Dunno what the setting of @prev does, so ignored it.

Always need to be careful with DATEPART and days of the week.here is a parameter that is used to help with days of week. Have a read of : http://msdn.microsoft.com/en-us/library/ms181598.aspx

So, if possible, use DATENAME if you are unsure of the DATEPART, or, explicitly set DATEFIRST in accordance with the above link (which you cannot do in a function, but can in a procedure).


ALTER FUNCTION [dbo].[udf_WKDELIM] (@dtDate DATETIME)
RETURNS DATETIME
AS
BEGIN
   IF DATENAME(weekday, @dtDate) in ('Sunday','Saturday') return dateadd(day,-2,@dtdate)
   RETURN @dtdate
END

Open in new window

0
 

Expert Comment

by:owenja
ID: 38839223
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
0
 

Expert Comment

by:owenja
ID: 38839239
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38839724
No worries :)
0
 

Author Comment

by:canteyhanger
ID: 38839869
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!
0
 
LVL 11

Expert Comment

by:Simone B
ID: 38839916
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

Open in new window


Image
0
 

Author Comment

by:canteyhanger
ID: 38839937
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.
0
 

Author Closing Comment

by:canteyhanger
ID: 38844184
To all, thank you for helping us with this.  We got it to work as expected.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question