• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5663
  • Last Modified:

T-SQL datetime equality comparison

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
aswam1975
Asked:
aswam1975
  • 2
1 Solution
 
grant300Commented:
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
 
bretCommented:
A computed column or function-based index (features in 15.x) to convert the datetime to just data are good solutions
0
 
grant300Commented:
Ther world will be a better place when most of us are running >=15 :-)

Bill
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now