RE: Create dates right on the hour interval-best technique

Jeff Moden

SSC Guru

Points: 1003894

September 4, 2015 at 3:49 pm

#1824785

mar.ko (9/4/2015)

Sorry guys, my bad....definitely having a bad day.

And DateAdd is probably efficient....as whenever strings are involved, the conversion is usually costly.

Lowell's solution is very slick and efficient.

Sean's solution is way out of my league...I can't even comprehend what is going on there.

I'll have to study it.

MUCH Better! 😛 We've all been there. As "Red Green" would say, "We're all in this together and I'm pullin' for ya".

Here is my little function to strip the time.
CREATE FUNCTION fnDateOnly
(
@FullDate datetime
)
RETURNS datetime
AS
BEGIN
-- Return the result of the function
RETURN CAST(FLOOR(CAST(@FullDate AS FLOAT)) AS DATETIME)
END

Except for the scalar function part of that, that's the second fastest method in SQL Server 2012. A faster method would be to just convert it to the DATE datatype (and possibly back again) and not use a function at all for "Date Only" conversions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)