Link to home
Start Free TrialLog in
Avatar of David L. Hansen
David L. HansenFlag for United States of America

asked on

A better way to write this function?

Below is the script I've already developed. It works, but seem a little long and heavy (for what it does). I thought there might be a more elegant way of putting this together. Note: TRUNCDATE() is a custom function which simply returns the date entered, except with a timestamp of midnight (hour:min:sec.millisec of 00:00:00.000 -- the beginning of the day). The function below just returns the input date also, except with a timestamp just a millisecond or so before midnight -- right at the end of the day.

Any thoughts on making this a more streamlined function? I'm not really worried about performance but it IS using five functions (with a few additional functions in truncdate).
CREATE FUNCTION [dbo].[DAYEND] (@DateValue Datetime) 
RETURNS datetime 
AS
BEGIN
	-- Declare the return variable here
	DECLARE @returnDate datetime  
				
	SET @returnDate = DATEADD(MILLISECOND,997,DATEADD(SECOND,59,DATEADD(MINUTE,59,DATEADD(HOUR,23,dbo.TRUNCDATE(@DateValue))))) 


	RETURN @returnDate

END

GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David L. Hansen

ASKER

I've taken your advice to heart and built the following function (so that I can still enjoy the clean/condensed feel of a BETWEEN statement without risking the pitfalls that come with it). Any thoughts?
CREATE FUNCTION [dbo].[BETWEENFULLDAYS] (@DateChecking datetime, @DateStart Datetime, @DateEnd Datetime) 
RETURNS BIT 
AS
BEGIN 
	IF (@DateChecking >= CAST(DATEADD(DAY,DATEDIFF(DAY,0,@DateStart),0) as date) AND @DateChecking < DATEADD(day, +1, CAST(DATEADD(DAY,DATEDIFF(DAY,0,@DateEnd),0) as date)))
		RETURN 1
RETURN 0 
END

GO

Open in new window

with that code you will get an accurate and reliable result

Im not sure why you need it as a scalar function or in what context it is to be used but if the boudary dates were defined as date you could avoid the casts but that may cause other issues
I chose a scalar Bit function just so I can continue to use something like the original BETWEEN. Is there another way I ought go about this (code reuse and basic laziness is basically the point)?  Also, come to think of it, I believe I can do without those CAST's.
Thank you all!
Is there another way I ought go about this?

probably.

Just use the general form of:

WHERE [col] >= '20120101' AND [col] < '20120201'

My fear is that you will use that function to create non-sargable predicates in where clauses like this:

WHERE [dbo].[BETWEENFULLDAYS] ([col], '20120101', '20120201')  = 1

i.e. you are needlessly running a scalar function on every row and the query cannot use indexes on [col]
Paul,

True enough. I see that. The situation at hand is that I'm using a reporting tool (built in-house) which does a super job and saves a great deal of time, but it has a few weaknesses. One of those is the fact that its date parameters automatically get the local machine's current time-stamp tacked on before being passed into my pre-made sql query. So, the BETWEENFULLDAYS function takes care of cutting off the timestamp and uses the preferred technique you've pointed to. Given those facts, I'm glad to avoid the use of a function, so as to take advantage of indexes, but not sure how. Those date parameters will still need to be used and they will still get a bad timestamp tacked on before being processed by sql-server.

If you'd like me to open a new question, I'd be happy to. But do you see anyway around this other than using the function (or lengthy manual formatting every time I use a date parameter -- it is a report generator tool after all)?
You know the local conditions and I don't. At this point I think I'm done :)

One advantage of opening another question is you get a set fresh of eyes to look over it, so feel free to do that.
Sounds good.  Again, thanks for the help!