[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1326
  • Last Modified:

SQL Query - Compare DateTime

Hi,

MyTable returns these columns when I do:

SELECT MyDate
From MyTable

2007-07-30 12:30:05.000
2007-07-25 12:45:10.000
..

How can I join MyTable to AnotherTable only using the dates (skip the time:

SELECT MyDate
From AnotherTable
2007-07-30 00:00:00.000
2007-07-25 00:00:00.000

F.ex.
SELECT MyDate
from MyTable mt
INNER JOIN AnotherTable at
on mt(only year,month,date) = at(only year,month,date)

Does anybody know how I can do this?

Thank you.
0
Endelm
Asked:
Endelm
1 Solution
 
Patrick MatthewsCommented:
SELECT MyDate
from MyTable mt
INNER JOIN AnotherTable at
on CONVERT(varchar, mt.MyDate, 102) = CONVERT(varchar, at.MyDate, 102)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT MyDate
from MyTable mt
INNER JOIN AnotherTable at
on CONVERT(VARCHAR,mt.MyDate,112) = CONVERT(varchar,at.DateColumn,112)
0
 
dbbishopCommented:
Either of the above will work with an equalities test. However, if you will be using any other type of test (<, >, BETWEEN..AND, >=, <=) you will want to use aneeshattingal's. In general practice, you'll typically use format 112 for any join.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Patrick MatthewsCommented:
dbbishop,

Excellent point on the inequality contingency.  That's what I get for posting before looking up the
paramter value for yyyy-mm-dd in Books Online :)

Related question (and this goes for aneeshattingal as well): will converting to varchar for the join
go faster, or are we better off from a performance standpoint using nested CONVERT() or
DATEADD() to keep the values as datetime but shear off the time portions?

Curious,

Patrick
0
 
EndelmAuthor Commented:
Thank you.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the advantage with 112 method is that the joining string will be smalles compared to the other, so will be faster
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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