Solved

T-SQL datetime equality comparison

Posted on 2006-11-02
3
5,585 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

What does UTC stand for?  “Coordinated Universal Time” – Think of this as the true time on Planet Earth that never changes with the exception of minor leap seconds here and there to account for the changes in the planet's rotation.   What does th…
Facing problems with you memory card? Cannot access your memory card? All stored data, images, videos are lost? If these are your questions...than this small article might help you out in retrieving your lost or inaccessible data.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now