Solved

T-SQL datetime equality comparison

Posted on 2006-11-02
3
5,610 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?
0
Comment
Question by:aswam1975
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 30 total points
ID: 17858968
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
0
 
LVL 10

Expert Comment

by:bret
ID: 17859471
A computed column or function-based index (features in 15.x) to convert the datetime to just data are good solutions
0
 
LVL 19

Expert Comment

by:grant300
ID: 17860562
Ther world will be a better place when most of us are running >=15 :-)

Bill
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Adults who share images on social media aren’t the only ones who need to worry about their privacy. Our culture’s tendency to share every move and celebration affects the privacy of our children, too.
Read our guide on how to survive being on-call.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question