CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008datetime-conversion
abatishchev
abatishchev

Reputation: 100268

Compare DATETIME and DATE ignoring time portion

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?

Upvotes: 179

Views: 318259

Answers (6)

Phuong Vu
Phuong Vu

Reputation: 51

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

Upvotes: 4

reza.cse08
reza.cse08

Reputation: 6178

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

Upvotes: 7

pim
pim

Reputation: 12577

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

Upvotes: 8

pankaj
pankaj

Reputation: 1906

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.....

Upvotes: -3

Disillusioned
Disillusioned

Reputation: 14832

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)

Upvotes: 65

marc_s
marc_s

Reputation: 754488

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)

Upvotes: 304

Related Questions