hi all,

i have a table with two fields

name, date_hired
a , 2004-04-16 10:02:31.000
B , 2004-04-18 20:02:31.000

i want a select statement that returns

a , 2004-04-16 00:00:00
B , 2004-04-18 00:00:00

Jose G. de Jesus Jr MCP, MCDBA wrote:

CAST(CONVERT(char(8), date_hired, 112) AS DATETIME)

thanks
Show quoteHide quote

Using cast and convert does work, but it's slow as sql needs to change the
datatype twice.
using this...

dateadd(day, datediff(day, 0, getdate()), 0)



instead means that SQL only using a floating point number.  It may seem a
little weird at first glance, but it does work.  The advantage is that SQL
does not need to convert the date to a different format. As a result of not
needing to convert to a different datatype it means that SQL does not need
to perform any logical reads/writes to do the conversion.  Also to make
things even better the entire routine is computed on the processor alone.
This has the net result of using no memory resources, and completely in what
I can only calculate as about 40 times faster than the cast/convert method.


Show quoteHide quote

Colin Dawson wrote:

Show quoteHide quoteHow sure are you about this?  Have you tested it?

I tried it out on a table with 5000 records and when you look at the
execution plan you'll see they are both 50%.

On Sat, 17 Dec 2005 02:41:46 GMT, Colin Dawson wrote:

Show quoteHide quoteHi Colin,

You are correct that this method is faster. But unfortunately, you are
incorrect on most of your other statements.

I have no inside knowledge of how your dateadd/datediff version is
evaluated, but I would be very surprissed if it used floating point
arithmetic. Since datetimes are stored as two integers, one for the date
part and one for the time part, discarding the second integer and doing
a substraction for the datediff or an addition for the dateadd would
make lots more sense.

Both version need data conversion. The data conversions from datetime to
string and back to datetime in Stijn's version are obvious; your version
has two implicit date conversions from integer (0) to datetime.

Neither Stijn's version nor your version has to do any logical or
physical I/O. Reads are only required when you access tables; writes are
only required when you perform UPDATE, INSERT or DELETE statements.

Both versions are computed entirely on the processor, but both do need
some memory, of course, to hold input, output and intermediate results
of the internal procedure to calculate the results.

Finally, the speed difference is nowhere near the "40 times faster" that
you claim. I tested this some months ago. I didn't keep the tests, but
it was easy enough to re-code and re-execute it. Here's the test script
I used, followed by the results on my machine. As you see, the test
consists of a loop (executed a million times). Inside the loop, the date
calculation is executed 5 times (I used 5, not 1, to minimize the effect
of the SET @Count = @Count + 1 and the comparison of @Coount against
100000 that are executed on each iteration of the loop).

-- Start with clean memory
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
go
-- Test version one (dateadd/datediff)
DECLARE @Start datetime
DECLARE @End datetime
DECLARE @Result datetime
DECLARE @Cnt int
SET @Cnt = 1
SET @Start = CURRENT_TIMESTAMP
WHILE @Cnt < 1000000
BEGIN
  SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
  SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
  SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
  SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
  SET @Result = DATEADD(day, DATEDIFF(day, 0, @Start), 0)
  SET @Cnt = @Cnt + 1
END
SET @End = CURRENT_TIMESTAMP
SELECT @Result AS Result,
       DATEDIFF(ms, @Start, @End) AS "Elapsed time"
go
-- Start with clean memory
CHECKPOINT
DBCC FREEPROCCACHE WITH NO_INFOMSGS
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS
go
-- Test version two (string conversion)
DECLARE @Start datetime
DECLARE @End datetime
DECLARE @Result datetime
DECLARE @Cnt int
SET @Cnt = 1
SET @Start = CURRENT_TIMESTAMP
WHILE @Cnt < 1000000
BEGIN
  SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
  SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
  SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
  SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
  SET @Result = CAST(CONVERT(char(8), @Start, 112) AS DATETIME)
  SET @Cnt = @Cnt + 1
END
SET @End = CURRENT_TIMESTAMP
SELECT @Result AS Result,
       DATEDIFF(ms, @Start, @End) AS "Elapsed time"
go

Results:

Result                                                 Elapsed time
------------------------------------------------------ ------------
2005-12-17 00:00:00.000                                15906

Result                                                 Elapsed time
------------------------------------------------------ ------------
2005-12-17 00:00:00.000                                61156

The difference between 15.906 seconds vs 61.156 seconds is significant,
but nowhere near "40 times faster". Four times faster is closer.

And finally - a speed difference that is only notable when you execute
the formula five milion times is not exactly a reason to go back and
change existing code. After all, for a single executioon of the formula,
the elapsed time is 3.18 microsecond vs 12.23 microseconds. In actual
code, the time needed to fetch rows is so much more that nobody will
ever notice any speed difference between the two in actual code.

Best, Hugo

so all you want is to get the midnight of the day? this is one of ways:

select name, convert(varchar(19), convert(datetime, convert(varchar(10),
date_hired, 120), 120), 120) date_hired
from theTable

no offence, but mcdba you put aside your name looks weird in this context.

peter


Show quoteHide quote

quite busy here.
Show quoteHide quote

Help with this trigger
Sum Total Hours and Minutes in a report
Help with multiple Left Joins
Joining two tables multiple times
please check