time after time

one table has a datetime in it.  The other has a dateID that is an … integer.  Sheesh.

I need to include the date into the join.  That is – i have a userid in the first table and want to join it to the second table, but i have LOTS of that userid in table two.  So i need also to restrict the join to the same date.  Turns out THAT isn’t even good enough, but don’t go there.

We need to coerse the integer into a date.  That we can accomplish with a cast as in

cast(dp.OfficeVisitDateID as datetime).  

Doesn’t work.  Can’t cast an integer this way.  First need to cast the integer into a varchar, then cast that into a datetime.  This works

(cast dp.OfficeVisitDateID as varchar(12)) as date)

oh,  you noticed?  the format of the the integer is 20130823, which means that we are ‘insecure’ about compairing that to something like ”2013-08-23 09:40:00.000”, so i am going to cast the datetime to a date so that i am more comfortable with comparing the two.

cast (@pm.appointment_datetime as date)

I tried to substract the two dates, but the result, when i expected them to be 0 was 1900-01-01 00:00:000, which i guess makes perfect sense, but harder to program.  The importance is that a join using the two dates works, so all is well in T-SQL Land.