Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dhanlak
Starting Member
5 Posts |
 Posted - 2011-11-18 : 06:54:33
|
| hi,I want to join 2 tables with date. I want to compare only the dates. That is excluding hours, minutes, seconds, millisecondsone 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 workwhere 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. |
|
|
umertahir
Posting Yak Master
154 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-11-18 : 08:58:46
|
The only reason for your compare (WHERE) not to work, is that your date values are using a varchar column and not a datetime column.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-18 : 10:49:47
|
quote:
Originally posted by umertahir
You might want to convert the date columns in a date only format and then compare:CONVERT(VARCHAR, tblA.DateColumn, 1) = CONVERT(VARCHAR, tblb.DateColumn, 1)Reference: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563
why do you need to convert to varchar for that?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-11-18 : 11:44:37
|
| CONVERT(DATE, tblA.DateColumn) = CONVERT(DATE, tblb.DateColumn)Or, if you preferDateAdd(Day, DateDiff(Day, 0, tblA.DateColumn), 0) = DateAdd(Day, DateDiff(Day, 0, tblB.DateColumn), 0)I'm not sure which would perform better but Guess'n'Test would yield that answer.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
dhanlak
Starting Member
5 Posts |
Posted - 2011-11-21 : 08:24:34
|
| Thanks a lot for the help!It works now. Actually i made a mistake. I was trying to match the id column too. |
|
|
|
|
|
|
|
|
compare date columns