Solved

A better way to write this function?

Posted on 2015-02-19
11
120 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
[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
11 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 total points
ID: 40619812
>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:Scott Pletcher
Scott Pletcher earned 167 total points
ID: 40619844
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
ID: 40620054
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Author Comment

by:David L. Hansen
ID: 40622147
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
ID: 40622261
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
 
LVL 15

Author Comment

by:David L. Hansen
ID: 40622294
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
ID: 40622347
Thank you all!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40622488
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
ID: 40629610
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
ID: 40629620
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
ID: 40629626
Sounds good.  Again, thanks for the help!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sorting a SQL script 5 38
T-SQL: Wrong Result 7 37
SQL Job Failed 6 29
Migrate SQL 2005 DB to SQL 2016 4 25
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

739 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