title: Left JOIN on DATE field not working in SQL
body: <p>This is an Order Management System scenario.</p>
  
  <p>I have a <code>[SalesHistory]</code> Table with following fields:<br>
  &nbsp;&nbsp;&nbsp;&nbsp;<strong>ItemMasterID</strong> <code>varchar(60)</code><br>
  &nbsp;&nbsp;&nbsp;&nbsp;<strong>Qty</strong> <code>int</code><br>
  &nbsp;&nbsp;&nbsp;&nbsp;<strong>OrderDate</strong> <code>DateTime</code><br>
  (Omitted non relevant fields)</p>
  
  <p>Executing following query on <code>SalesHistory</code>:</p>
  
  <pre><code>select Cast(OrderDate as Date) 'OrderDate',Sum(Qty) 'Qty'  from SalesHistory 
  where itemmasterid= '001FCB08-B72E-4D1A-91D6-C3D8A8CF7008' 
  Group by ItemMasterId ,Cast(OrderDate as DAte)
  order by OrderDate desc
  </code></pre>
  
  <p>I get the following results (As expected)<br>
  <a href="https://i.stack.imgur.com/fERQs.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fERQs.png" alt="enter image description here"></a></p>
  
  <p>As you can see, for the month of <code>04</code> there are only 4 records.<br>
  What I expect is that all the days of a month be displayed on the left side (Date column) with zeros on the right side (Qty column) if there is no record for that particular date</p>
  
  <hr>
  
  <p><em>Here is what I tried:</em></p>
  
  <p>I created a table <code>[Date_Ref]</code> with field:<br>
  &nbsp;&nbsp;&nbsp;&nbsp;<strong>Day</strong> <code>DateTime</code></p>
  
  <p>Then I filled it up with all days of month <code>04</code> using the following procedure:</p>
  
  <pre><code>declare @SD Date = convert(Date,'04/01/2017',101)
  declare @ED Date = convert(Date,'04/30/2017',101)
  declare @DatePeriod Date = @SD
  while (@DatePeriod &lt;= @ED)
  begin
      insert into Date_Ref (Day) values (@DatePeriod)
      set @DatePeriod = DATEADD(DD, 1,@DatePeriod)
  end
  </code></pre>
  
  <p>Now the <code>Date_Ref</code> table is filled with expected records:</p>
  
  <pre><code>select Day 'Date' from Date_Ref
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/rfL4x.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rfL4x.png" alt="enter image description here"></a></p>
  
  <hr>
  
  <p>I tried using <code>LEFT JOIN</code> on this and <code>SalesHistory</code> table expecting to get the result I mentioned above:</p>
  
  <pre><code>select Cast(d.Day as Date) 'Date',ISNULL (s.Qty,0) 'Qty' 
  from date_Ref as d 
  left join 
  SalesHistory as s on Cast(s.OrderDate as date) = cast(d.Date_Period as date) 
  where 
  s.itemmasterid = '001FCB08-B72E-4D1A-91D6-C3D8A8CF7008'
  </code></pre>
  
  <p>and I got his result:<br>
  <a href="https://i.stack.imgur.com/zq7wU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zq7wU.png" alt="enter image description here"></a></p>
  
  <hr>
  
  <p>Clearly this is not what I EXPECTED.!! I excepected something like:</p>
  
  <pre><code>+-------------+----+
  | Date        | Qty|
  +-------------+----+
  | 2017-04-01  |   0|
  | 2017-04-02  |   0|
  | 2017-04-03  |   0|
  | 2017-04-04  |   0|
  | 2017-04-05  |   0|
  | 2017-04-06  |   0|
  | 2017-04-07  |   0|
  | 2017-04-08  |   0|
  | 2017-04-09  |   0|
  | 2017-04-10  |   1|
  | 2017-04-11  |   1|
  ....
  ....
  </code></pre>
  
  <hr>
  
  <p>Why is the <code>LEFT JOIN</code> behaving like an <code>INNER JOIN</code>? Or is this how SQL returns when you join on <code>DateTime</code> field?</p>
  
  <p><strong>Note</strong>:<br>
  I also tried removing the <code>Cast</code>.<br>
  I also tried all the joins <code>LEFT</code> <code>RIGHT</code> <code>OUTER</code> <code>INNER</code>.<br>
  I also tried switching positions of <code>SalesHistory</code> and <code>Date_Ref</code> tables in the query.</p>
  
  <p>Is it that I am doing something wrong? Or is there another approach to this?</p>
  
  <p>Thankyou</p>
  
  <p><em>Sorry for the lengthy question!! I just had to describe the exact steps what I did</em></p>