We help IT Professionals succeed at work.

T-SQL datetime equality comparison

aswam1975
aswam1975 asked
on
Medium Priority
5,727 Views
Last Modified: 2012-06-21
I would like to check wethere 2 columns which are of type dateime have the same date.When I test ,I see that
table1.dateT1 = table2.date2 does not work,even though they have the same date time/ Should I have to convert to a string to do the same?
Comment
Watch Question

Commented:
The problem is the precision of the date/time.  They are accurate to the nearest 1/300th of a second.  As a result, if you want to compare dates, you either have to round the dates or convert to strings with just the date and no time

Be aware, putting functions into SARGs will frequently prevent the optimizer from using available indexes.  Depending upon how much data you have and the performance requirements for this query, you may find it best to add a date field with just the pre-rounded date on at least one of the two tables.

Bill

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
A computed column or function-based index (features in 15.x) to convert the datetime to just data are good solutions

Commented:
Ther world will be a better place when most of us are running >=15 :-)

Bill
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.