SQL Server 2000 - Stripping time from datetime column results using DATEADD and DATEDIFF is returning result with time still?

I know there are several posts about stripping the time portion from a datetime column. And everyone suggests that the DATEADD and DATEDIFF method is the best for performance and individuals should not adopt the process of cast/converting date values to varchar. So I was wanting to utilize the functions below but I don't understand why the result I receive still has the time value associated to it though the time is all zeroes?

To provide more information as to why I want to understand this result. I have a stored procedure that I wish to specify the following:

DECLARE @DateShippedBegin DATETIME
DECLARE @DateShippedEnd DATETIME
SET @DateShippedBegin = dateadd(dd, datediff(dd, 0, @DateShipped), 0)
SET @DateShippedEnd = dateadd(dd, datediff(dd, 0, @DateShipped) + 1, 0)

WHERE O.date_shipped >= @DateShippedBegin and O.date_shipped < @DateShippedEnd

But when I execute the procedure with a basic date format I receive no results so trying to understand the best concept to adopt for my procedure.

Select DATEADD(dd, DATEDIFF(dd, 0, Getdate()), 0) from orders where order_no = 
'1247401'

Example of begginning date value:

2017-05-010 08:40:18.287

Example of query result:

2017-05-10 00:00:00.000

A datetime column always includes a time part. If you set a datetime column to '2017-05-10', it'll show up as 2017-05-10 00:00:00.

When you want to compare "dates only" with datetime columns, you set the time part to midnight on both dates. This is generally referred to as "removing" the time part.

If you want to eliminate the time part from your output, there are various options. You can build a date in whatever format you wish by pulling the various fields out with DATEPART. Most commonly, however, I've seen people convert the date to a string and drop the time portion:

SELECT CONVERT(varchar(10), GETDATE(), 120);

Note that CONVERT is being used here for formatting the date value for output, not for changing it for comparison purposes. I'd still do that as you did in your code.

StackExchange Database Administrators Q#173241, answer score: 6