cal number of days in between dates

select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= '01/01/2004' and convert(varchar,FileClosedDate,101) < '31/12/2004'

Hi I was wondering if somebody could help me alter this query so I an calulate the difference in days in between DateEntered and FileClosedDate having the above criteria.
I can't seem to be able to get the datediff function right in this particular examplewhy are you converting FileClosedDate to varchar?

let the database compare dates as dates ;) :)

what are the datatypes of the two date columns?|||Try using # Sign before and After Dates like
select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= #01/01/2004# and convert(varchar,FileClosedDate,101) < #31/12/2004#|||Try using # Sign before and After Dates like
select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= #01/01/2004# and convert(varchar,FileClosedDate,101) < #31/12/2004#

This is the SQL Server forum, not the MS Access forum, darvesh...

C# and SQL Server 2005, Same Query Different Results (More Rows)

Hi,

I execute this query in SQL Server 2005 Management Studio:

SELECT c.id, c.clientname
FROM NFClients AS c INNER JOIN
NFExportGroups AS eg ON eg.cid = c.id INNER JOIN
NFExportGroupInfo AS ei ON ei.exportid = eg.exportid
WHERE (ei.meterid = 4938) AND (NOT (eg.emailgroup IS NULL))

I get one row returned and it is correct.

Then I go to Visual Studio 2005, to my C-Sharp application, and I use the same query to populate two indexes of 'rowvalues', my trusty two-dimensional string array:

Code Snippet

SqlCommand sqlcmdExx = new SqlCommand("SELECT c.id, c.clientname from NFClients AS c INNER JOIN " +

"NFExportGroups AS eg ON eg.cid = c.id INNER JOIN NFExportGroupInfo AS ei ON ei.exportid = eg.exportid " +

"WHERE (ei.meterid = " + comparedgroupids[i, 2].ToString() + ") AND (NOT (eg.emailgroup IS NULL))", sqlconnBuildDataGridViewSet);

SqlDataReader sqldrExx = sqlcmdExx.ExecuteReader();

while (sqldrExx.Read())

{

rowvalues[i, 2] = sqldrExx.GetValue(1).ToString();

rowvalues[i, 3] = sqldrExx.GetValue(0).ToString();

}

sqldrExx.Close();

This time, the SqlDataReader picks up two rows, and the second one, with a different c.id and c.clientname does have a NULL value for eg.emailgroup. I have verified that the value of comparedgroupids[i, 2] is 4938, and I have also confirmed that sqlcmdExx.CommandText is exactly the same as the query I run in the SQL Server Management Studio. Why do I get different results with same query on the same database?

Thanks.

Dale

I'm not sure as to the cause of the difference but try using "(eg.mailgroup IS NOT NULL)" instead of (NOT (eg.emailgroup IS NULL))|||

Hi,

Found out this morning it was my mistake, I was looking at the wrong copy of the database. Sorry about that.

Dale