[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 128
  • Last Modified:

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

0
David L. Hansen
Asked:
David L. Hansen
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Scott PletcherSenior DBACommented:
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
 
PortletPaulCommented:
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!

 
David L. HansenProgrammer AnalystAuthor Commented:
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
 
PortletPaulCommented:
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
 
David L. HansenProgrammer AnalystAuthor Commented:
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
 
David L. HansenProgrammer AnalystAuthor Commented:
Thank you all!
0
 
PortletPaulCommented:
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
 
David L. HansenProgrammer AnalystAuthor Commented:
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
 
PortletPaulCommented:
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
 
David L. HansenProgrammer AnalystAuthor Commented:
Sounds good.  Again, thanks for the help!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now