175

I have two tables where column [date] is type of DATETIME2(0).

I have to compare two records only by theirs Date parts (day+month+year), discarding Time parts (hours+minutes+seconds).

How can I do that?

Salman A
  • 262,204
  • 82
  • 430
  • 521
abatishchev
  • 98,240
  • 88
  • 296
  • 433

6 Answers6

293

Use the CAST to the new DATE data type in SQL Server 2008 to compare just the date portion:

IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
64

A small drawback in Marc's answer is that both datefields have been typecast, meaning you'll be unable to leverage any indexes.

So, if there is a need to write a query that can benefit from an index on a date field, then the following (rather convoluted) approach is necessary.

  • The indexed datefield (call it DF1) must be untouched by any kind of function.
  • So you have to compare DF1 to the full range of datetime values for the day of DF2.
  • That is from the date-part of DF2, to the date-part of the day after DF2.
  • I.e. (DF1 >= CAST(DF2 AS DATE)) AND (DF1 < DATEADD(dd, 1, CAST(DF2 AS DATE)))
  • NOTE: It is very important that the comparison is >= (equality allowed) to the date of DF2, and (strictly) < the day after DF2. Also the BETWEEN operator doesn't work because it permits equality on both sides.

PS: Another means of extracting the date only (in older versions of SQL Server) is to use a trick of how the date is represented internally.

  • Cast the date as a float.
  • Truncate the fractional part
  • Cast the value back to a datetime
  • I.e. CAST(FLOOR(CAST(DF2 AS FLOAT)) AS DATETIME)
Disillusioned
  • 14,635
  • 3
  • 43
  • 77
7

Though I upvoted the answer marked as correct. I wanted to touch on a few things for anyone stumbling upon this.

In general, if you're filtering specifically on Date values alone. Microsoft recommends using the language neutral format of ymd or y-m-d.

Note that the form '2007-02-12' is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.

To do a date comparison using the aforementioned approach is simple. Consider the following, contrived example.

--112 is ISO format 'YYYYMMDD'
declare @filterDate char(8) = CONVERT(char(8), GETDATE(), 112)

select 
    * 
from 
    Sales.Orders
where
    CONVERT(char(8), OrderDate, 112) = @filterDate

In a perfect world, performing any manipulation to the filtered column should be avoided because this can prevent SQL Server from using indexes efficiently. That said, if the data you're storing is only ever concerned with the date and not time, consider storing as DATETIME with midnight as the time. Because:

When SQL Server converts the literal to the filtered column’s type, it assumes midnight when a time part isn’t indicated. If you want such a filter to return all rows from the specified date, you need to ensure that you store all values with midnight as the time.

Thus, assuming you are only concerned with date, and store your data as such. The above query can be simplified to:

--112 is ISO format 'YYYYMMDD'
declare @filterDate char(8) = CONVERT(char(8), GETDATE(), 112)

select 
    * 
from 
    Sales.Orders
where
    OrderDate = @filterDate
pim
  • 12,019
  • 6
  • 66
  • 69
6

You can try this one

CONVERT(DATE, GETDATE()) = CONVERT(DATE,'2017-11-16 21:57:20.000')

I test that for MS SQL 2014 by following code

select case when CONVERT(DATE, GETDATE()) = CONVERT(DATE,'2017-11-16 21:57:20.000') then 'ok'
            else '' end
reza.cse08
  • 5,938
  • 48
  • 39
4

You may use DateDiff and compare by day.

DateDiff(dd,@date1,@date2) > 0

It means @date2 > @date1

For example :

select DateDiff(dd, '01/01/2021 10:20:00', '02/01/2021 10:20:00') 

has the result : 1

DuDa
  • 3,718
  • 4
  • 16
  • 36
Phuong Vu
  • 51
  • 2
-3

For Compare two date like MM/DD/YYYY to MM/DD/YYYY . Remember First thing column type of Field must be dateTime. Example : columnName : payment_date dataType : DateTime .

after that you can easily compare it. Query is :

select  *  from demo_date where date >= '3/1/2015' and date <=  '3/31/2015'.

It very simple ...... It tested it.....

pankaj
  • 1
  • 17
  • 36
  • This does not quite answer the question. It queries for a month, not a day. – Curtis Yallop Jul 21 '16 at 21:16
  • For your March query: If the date is '3/31/2015' time 13:00, it will not be found. You should use < '4/1/2015'. – Curtis Yallop Jul 21 '16 at 21:16
  • Also consider using international date format '2015-03-01'. In Canada (and many other places), an official format is DD/MM/YYYY which makes both formats ambiguous and problematic. – Curtis Yallop Jul 21 '16 at 21:20
  • This answer does not deal with 2 tables, as requested in the question. – Curtis Yallop Jul 22 '16 at 17:06
  • Yes i m agree ,but i m just proving solution not international solution.. you have to change some order ... – pankaj Jul 23 '16 at 13:43
  • Question was only " Compare two DATETIME only by date not time in SQL Server 2008 " – pankaj Jul 23 '16 at 13:44
  • **1)** Your answer doesn't compare 2 datetime values. It compares a single value to 2 constants. **2)** Your answer fails to meet the requirement of ignoring the time portion when doing the comparison. **3)** Your claim that "_Field must be datetime_" is completely wrong. – Disillusioned Aug 12 '16 at 08:16