How to strip time from datetime column

Question

I have a query that I'm running and I only want to consider the date, not the time, when comparing two columns. I currently have:

SELECT dtvisit1, dtvisit2
  FROM dbo.Visit
  WHERE dtvisit1 = dtvisit2;

Obviously this will only display the rows where each visit was at the SAME time on the SAME day. I just want to know if both columns had entries on the same date.

This question has been answered pretty thoroughly here: http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server — Display Name is missing, Mar 22 '13 at 16:24

Aaron Bertrand · Answer 1 · 2013-03-22T16:46:39.457

8

The most efficient way, if there is an index on dtvisit1 is probably going to be:

WHERE dtvisit1 >= DATEDIFF(DAY, 0, dtvisit2)
  AND dtvisit1 <  DATEDIFF(DAY, -1, dtvisit2)

If there is an index on dtvisit2 then swap it around. This will still allow you to use an index on one side at least. If there is no index you can use other methods like converting to a string, though you run the risk of still requiring a full scan later even after you add an index to one or both columns. The convert to a string approach seems to be the knee-jerk reaction for most folks, however I demonstrate in this blog post why that is the last thing you want to do.

You might also consider adding a computed column, such as:

ALTER TABLE dbo.Visit
  ADD visit1c AS (DATEDIFF(DAY, 0, dtvisit1));

ALTER TABLE dbo.Visit
  ADD visit2c AS (DATEDIFF(DAY, 0, dtvisit2));

Then you could just say WHERE visit1c = visit2c. Even simpler would be:

ALTER TABLE dbo.Visit
  ADD visitc AS (DATEDIFF(DAY, dtvisit1, dtvisit2));

Then you could say WHERE visitc = 0.

You may want to investigate persisting and/or indexing them.

In SQL Server 2008, you could simply convert both sides to DATE, without losing sargability.

edited Mar 22 '13 at 16:46

answered Mar 22 '13 at 16:33

Aaron Bertrand

272,866
37
466
490

I keep seeing this logic presented as a way of stripping the time from a datetime field, but when I run select DATEADD(day, 0, getdate()), I get the time component. What am I missing? – Dan Bracuk Mar 22 '13 at 17:29
1

@Dan you'll notice I use `DATEDIFF`, not `DATEADD`. Try `SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);`. Above the computed columns are just integers representing the number of day boundaries have been crossed. And the `WHERE` clause does an implicit convert, e.g. try `DECLARE @d DATETIME = DATEDIFF(DAY, 0, GETDATE()); SELECT @d;`. – Aaron Bertrand Mar 22 '13 at 17:31
I did notice that but when I ran that query I saw an integer and got confused. You'll have to forgive me, I'm very easily confused. – Dan Bracuk Mar 22 '13 at 17:51
1

@DanBracuk that's OK, there is some magic here. A `DATETIME` value is composed internally of two integers. The important one in most cases is the number of days since `1900-01-01` (aka "the dawn of time" or "zero date"). So try: `SELECT CONVERT(DATETIME, 0);` and `SELECT CONVERT(DATETIME, 41353);`. It is no coincidence that 41,353 days have passed since `1900-01-01`. These are shorthand methods that are convenient in SQL Server 2005, but in all honesty shouldn't be used in 2008 and up (if you switch to `DATE`, for example, they break down). – Aaron Bertrand Mar 22 '13 at 17:57

score -2 · Answer 2 · answered Mar 22 '13 at 16:19

-2

Use the convert() function in your where clause. Make sure you use the same format both times.

answered Mar 22 '13 at 16:19

Dan Bracuk

20,699
4
26
43

Buck Hicks · Accepted Answer · 2013-03-22T16:27:23.367

-3

You could use Convert to make the date a nvarchar and then do the comparison like this:

SELECT dtvisit1, dtvisit2
FROM Visit
WHERE CONVERT(nvarchar(20), [dtvisit1], 101)  = CONVERT(nvarchar(20), [dtvisit2], 101)

edited Mar 22 '13 at 16:27

answered Mar 22 '13 at 16:19

Buck Hicks

1,534
16
27

5

Converting dates to strings is generally a pretty terrible idea. – JNK Mar 22 '13 at 17:09

How to strip time from datetime column

3 Answers3