| Author |
Topic |
|
techdawg270
Starting Member
4 Posts |
 Posted - 2010-08-25 : 15:59:18
|
I am trying to compare dates from two different tables and running into an issue. One of the tables has an associated time with the date, and the other does not. The code looks like this: select gbd.client_id, gbd.val_date, cbd.date_added, cbd.date_updatedinto #clientsToInactivatefrom #tempGetBirthDate gbdjoin #tempCheckBirthDate cbdon gbd.client_id = cbd.uidwhere gbd.val_date = cbd.date_added An example of a date from #tempGetBirthDate would be 2003-10-07 00:00:00.000, and from #tempCheckBirthdate would be 2003-10-07 20:17:36.000. How can I go about ignoring the time stamp and do the comparison on just the yyyy-mm-dd? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-25 : 16:09:19
|
where gbd.val_date = dateadd(dd,datediff(dd,0,cbd.date_added),0)
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
techdawg270
Starting Member
4 Posts |
Posted - 2010-08-25 : 16:30:47
|
You are a genius webfredThank you for your assistance  |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-25 : 18:27:45
|
welcome
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
glendcruz
Yak Posting Veteran
60 Posts |
Posted - 2010-08-29 : 00:29:29
|
| The best solution is to convert the date to a string and back again to truncate the time information. In the eg, If you alter the date you wont get any data displayedEg :select * from <tablename>where convert(varchaR(12),<column name of date type>, 101) = convert(varchar(12),<date to be compared>, 101)Better to create a function and use it when required.CREATE FUNCTION dbo.ComparingDates(@date datetime)RETURNS varchar(12)ASBEGIN RETURN CONVERT(varchar(12), @date,101)END-- Your example tried out in various stagesdeclare @tempGetBirthDate table( id smallint, dob datetime)insert into @tempGetBirthDate select 1,'2003-10-07 00:00:00.000'declare @tempCheckBirthdate table( id smallint, dob datetime) insert into @tempCheckBirthdateselect 1,'2003-10-07 20:17:36.000'select * from @tempGetBirthDateselect * from @tempCheckBirthdateselect bd.dob as 'Birthdate', cd.dob as 'check date' from @tempGetBirthDate bdjoin @tempCheckBirthdate cdon bd.id = cd.idwhere dbo.ComparingDates(bd.dob) = dbo.ComparingDates(cd.dob)----------------(1 row(s) affected)(1 row(s) affected)id dob------ -----------------------1 2003-10-07 00:00:00.000(1 row(s) affected)id dob------ -----------------------1 2003-10-07 20:17:36.000(1 row(s) affected)Birthdate check date----------------------- -----------------------2003-10-07 00:00:00.000 2003-10-07 20:17:36.000(1 row(s) affected) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-08-29 : 06:53:23
|
quote:
The best solution is to convert the date to a string and back again to truncate the time information
I don't think that convert to string and back is THE BEST solution.My given solution is stripping the time part without converting.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
glendcruz
Yak Posting Veteran
60 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-30 : 03:14:12
|
| Well...the example is with temp-table and for this case my recommendation will be obsolete but if the two tables were permanent none of the two solutions are optimal. I agree that webfreds method is the better of the two but as far as I know it will still create a full table scan due to the dateadd/datediff-functions. If this comparison if done very frequently the best would be to create an additional column in the table that has the time also, either as a persisted computed column or just a regular column that is populated/updated along with the "parent"-column.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Problem comparing dates