compare date columns

Question

compare date columns

dhanalakshmi 99938

Mr or Mrs. 500

Points: 594
More actions
November 18, 2011 at 4:56 am

#246629

hi,
I want to join 2 tables with date. I want to compare only the dates. That is excluding hours, minutes, seconds, milliseconds
one table contains a date column with value '2011-05-05 08:00:00.000'
another table contains date column with 2 matching rows
'2011-05-05 08:00:00.000' and
'2011-05-05 08:12:14.000'
But, the join matches only the row with '2011-05-05 08:00:00.000'. The row with the date '2011-05-05 08:12:14.000' is omitted.
-----
I used this in where clause, but did not work
where convert(date,tbl1.datecol) = convert(date,tbl2.datecol)
------
I dont want to use YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()) and compare individually. IS there any other useful way to achieve this.
Please reply asap.
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

dhanalakshmi 99938 (11/18/2011)
hi,
I want to join 2 tables with date. I want to compare only the dates. That is excluding hours, minutes, seconds, milliseconds
one table contains a date column with value '2011-05-05 08:00:00.000'
another table contains date column with 2 matching rows
'2011-05-05 08:00:00.000' and
'2011-05-05 08:12:14.000'
But, the join matches only the row with '2011-05-05 08:00:00.000'. The row with the date '2011-05-05 08:12:14.000' is omitted.
-----
I used this in where clause, but did not work
where convert(date,tbl1.datecol) = convert(date,tbl2.datecol)
------
I dont want to use YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()) and compare individually. IS there any other useful way to achieve this.
Please reply asap.
Thanks in advance.

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

RAND(CHECKSUM(NEWID())) * 100000 + CAST('2000' AS DATETIME) AS randomDate

INTO #testEnvironment

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

IF object_id('tempdb..#testEnvironment2') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment2

END

--1,000,000 Random rows of data

SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,

RAND(CHECKSUM(NEWID())) * 100000 + CAST('2000' AS DATETIME) AS randomDate

INTO #testEnvironment2

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

SELECT a.*, b.*

FROM #testEnvironment a

INNER JOIN #testEnvironment2 b ON DATEADD(DAY, 0, DATEDIFF(DAY, 0, a.randomDate)) = DATEADD(DAY, 0, DATEDIFF(DAY, 0, b.randomDate))

So I joined the two tables like this -

DATEADD(DAY, 0, DATEDIFF(DAY, 0, a.randomDate)) = DATEADD(DAY, 0, DATEDIFF(DAY, 0, b.randomDate))

There are plenty of other ways.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

this works for me,

create table #dates1 (Date1 DATETIME)

create table #dates2 (Date2 DATETIME)

insert into #dates1 values ('2011-05-05 08:00:00.000')

insert into #dates2 values ('2011-05-05 08:00:00.000')

insert into #dates2 values ('2011-05-05 08:12:14.000')

select

d1.*,

d2.*

from

#dates1 d1

inner join

#dates2 d2

on

CONVERT(date, d1.date1) = CONVERT(date, d2.date2)

drop table #dates1

drop table #dates2

dhanalakshmi 99938 Mr or Mrs. 500 Points: 594 More actions · Answer 3

Thanks a lot for the help!

It works now. Actually i made a mistake. I was trying to match the id column too.