Solved

A better way to write this function?

Posted on 2015-02-19
11
116 Views
Last Modified: 2015-02-24
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

0
Comment
Question by:David L. Hansen
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 total points
Comment Utility
>simply returns the date entered, except with a timestamp of midnight
CAST(your_datetime as date) will also return just the date part with the midnight that is the beginning of that day.

>except with a timestamp of midnight (hour:min:sec.millisec of 00:00:00.000 -- the beginning of the day).
DATEADD(ms, -1, DATEADD(day, 1, CAST(your_datetime as date))) will do that,

BUT

If this is being done to support a BETWEEN date comparison, I'd recommend the below instead
WHERE some_date >= CAST(your_datetime as date)
   AND some_date < DATEADD(day, 1, CAST(your_datetime as date))

A good read here would be SQL expert PortletPaul's article BEWARE of BETWEEN
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 167 total points
Comment Utility
Rather than try to find the "end" time, use < the next full day, as that will continue to work correctly even if the column type changes; for example, to datetime2.

The preferred way to strip the time is this:
DATEADD(DAY, DATEDIFF(DAY, 0, <datetime_column>), 0)
That's mathematical only, and thus runs very quickly.

When scalar functions are needed, whenever possible they should consist of a single RETURN statement, with no local variables (other than params), because variables slow down processing.  Finally, you have to subtract 3 ms, not 1, as only 1 will round back up to midnight of the next day.


CREATE FUNCTION [dbo].[DAYEND] (@DateValue Datetime)
RETURNS datetime
AS
BEGIN
RETURN (
    SELECT DATEADD(MILLISECOND, -3, DATEADD(DAY, DATEDIFF(DAY, 0, @DateValue) + 1, 0))
)
END
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
Comment Utility
You should not be attempting use of "end of the day" and even at 3 miliseconds that could be inaccurate in encountering datetime2 information (with an accuracy of 100ns).

NEVER use "between" for date ranges.

ALWAYS use >= with < instead.

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.


Itzik Ben-Gan
sqlmag.com/t-sql/t-sql-best-practices-part-2


So, stop trying to calculate the end of that day, just use the beginning of the next day instead.
0
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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

0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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.
0
 
LVL 15

Author Closing Comment

by:David L. Hansen
Comment Utility
Thank you all!
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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]
0
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
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)?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 15

Author Comment

by:David L. Hansen
Comment Utility
Sounds good.  Again, thanks for the help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now