David L. Hansen
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).
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
ASKER
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]
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]
ASKER
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)?
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.
One advantage of opening another question is you get a set fresh of eyes to look over it, so feel free to do that.
ASKER
Sounds good. Again, thanks for the help!
ASKER
Open in new window