5

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
  • I strongly suggest getting in the habit of spelling out things like day instead of using problematic shorthand like dd - for what? To save one character? Why? See why this habit is dangerous and I also mention it here. – Aaron Bertrand May 10 '17 at 16:22
  • To be honest it was code found in the forums on trying to understand the concept of DATEADD and DATEDIFF. Thanks for suggesting this, I do agree its a better user understanding when someone has to review the code. – tfenwick11 May 10 '17 at 16:27
6

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.

  • With returning a high # of rows (say 10,000), is using the "convert" method the most efficient way with regards to performance and still adopting results with receiving dates only? – tfenwick11 May 10 '17 at 14:55
  • To give a better understanding I have a crystal report in which users are selecting a parameter in which they specify a day for results to return (Can be thousands). And from my stored procedure I wanted to include a variable that allows a date (without time) to be adopted as input. But from what you are saying its probably best to convert the datetime field to varchar as just a date and then based off those results allow a date input? – tfenwick11 May 10 '17 at 15:00
  • In SQL Server 2000, almost certainly - building the date yourself would involve function calls to get the year, month, and day, and then casting those values to varchar anyway. In SQL 2008 on, CAST(GETDATE() as DATE) might be faster. – RDFozz May 10 '17 at 15:01
  • Again, given SQL 2000, I'd take a datetime value as a parameter to the stored procedure, shave the time down to midnight as a precautionary measure, and work with the datetime value, only converting to varchar when I have to for display purposes. I haven't worked with Crystal Reports extensively, but it might be able to trim time off a datetime field at its end (generally, most people recommend that formatting be the responsibility of the application, not the DB). – RDFozz May 10 '17 at 15:04
1

The reason is because that field is still a DATETIME datatype. If you want the output to only show the date, you'll need to CONVERT the output to a varchar (or char in this case), such as:

Select CONVERT(CHAR(10), DATEADD(dd, DATEDIFF(dd, 0, Getdate()), 0), 101) from orders where order_no = 
'1247401'
  • (Further, since it never fluctuates, I would use char instead of varchar. :-)) – Aaron Bertrand May 10 '17 at 16:49
  • LOL, this really is one of your pet-peeves – John Eisbrener May 10 '17 at 16:52
  • I have a handful, sorry – Aaron Bertrand May 10 '17 at 16:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.