C# and SQL Server 2005

Question

I have 4 tables from which I am fetching records. The first three tables required inner join with the common rows but with 4th table I need left outer join because I have to preserve the already fetched rows from first three tables.

Now, problem is that I want left join with fourth table but it should only pick the records having date = current date. I am passing current date from C# while calling my stored procedure. But the thing is SQL Server is giving null records.

Here is the query::

alter procedure
  (@currentdate varchar(50))
  select distinct 
      table1.name, table2.class,table3.stream 
  from 
      table1
  inner join 
      table 2 on table1.id = table2.id 
  inner join 
      table3 on table3.id=table2.id   
  left outer join 
      table 4 on table 4.id = table3.id and table4.date = @currentdate

I used and with left outer join because I want records in that way where as using where clause filters the whole result and just give me one record.

Then also I used casting with both the dates in stored procedure but no positive response.

From C# I'm using

var date = datetime.now.toshortdate();
CallStoredProcedure(date)

Please help me regarding this ASAP. Thank you

Answer 1

First of all - you should use the appropriate type for your dates - that is DATETIME - do not cast dates into strings and back if you can avoid it! (and you can definitely avoid it here)

Secondly: the DATETIME in SQL Server as well as the DateTime in .NET both have dates and time information, e.g. 2013-06-23 14:57:34 so if you're doing an equality comparison in your JOIN condition, only those rows that exactly match your date and time will be selected. So you need strip off the time portion, if you want to use just dates for comparison.

So try this code here:

ALTER PROCEDURE dbo.YourProcedureNameHere (@currentdate DATETIME)
  SELECT DISTINCT
      table1.name, table2.class, table3.stream 
  FROM
      dbo.table1
  INNER JOIN
      dbo.table2 ON table1.id = table2.id 
  INNER JOIN
      dbo.table3 ON table3.id = table2.id   
  LEFT OUTER JOIN
      table 4 ON table 4.id = table3.id 
              AND DATEADD(DAY, DATEDIFF(DAY, 0, table4.date), 0) = 
                  DATEADD(DAY, DATEDIFF(DAY, 0, @currentdate), 0)

This strips off the time portion of your table4.date as well as @currentdate and then the comparison will be done on just the date (no time involved). Once you've updated to SQL Server 2008 or newer, I would recommend to use the DATE datatype which is designed exactly for this - storing just the date (no time). Unfortunately, that's not yet available in SQL Server 2005.

Also, from C# - use this line to call your stored procedure:

var date = Datetime.Today;
CallStoredProcedure(date);