Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-SQL datetime equality comparison

Posted on 2006-11-02
3
Medium Priority
?
5,657 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 120 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
Transferring FSMO roles is done when an admin wants to split roles between certain Domain Controllers or the Domain Controller holding the Roles has been forcefully demoted using dcpromo / forceremoval
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

972 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