Calculate Number of Days

I want to calculate the number of days from a field 'complaintdate' 开发者_开发知识库in my SQL database. In this field store the date in which users register complaint. I want to see the complaints pending 20 days and more from current date.

Is there is any other function or method to count number of days? the code i used is

cmd = new SqlCommand("select complaintdate from complaintregister where compstatus='Attended & Open'", con);
rd = cmd.ExecuteReader();
if (rd.Read())
{
    string s = rd["complaintdate"].ToString();
}

i displayd 's' by

Response.Write(s);

and its diplayed in this format 4/5/2011 12:00:00 AM

`

From your question, I'm not sure if you want to do this comparison in the database or in code.

If you want to do the check in SQL Server, you can use the DATEDIFF function.

If you want to do the check in code, you can use DateTime.Subtract:

var complaintDate = new DateTime(2011, 9, 1);
var daysSinceComplaint = (int)DateTime.Now.Subtract(complaintDate).TotalDays;
Console.WriteLine(daysSinceComplaint);

This will retrieve totalDays as the number of days between your complaintDate and today. Replace GetDateFromDatabase() with your means of retrieval:

DateTime complaintDate = GetDateFromDatabase();
int totalDays = (int)(DateTime.Now - complaintDate).TotalDays;
if (totalDays >= 20)
{
    // Perform your actions here. 20+ days have elapsed.
}

EDIT:

I have added the following code to use the code you provided in your edit. I am assuming variables cmd, con, and rd are declared elsewhere in your code.

cmd = new SqlCommand("select complaintdate from complaintregister where compstatus='Attended & Open' ", con);`
rd = cmd.ExecuteReader();
if (rd.Read())
{
    string s = rd["complaintdate"].ToString();
}

DateTime complaintDate;
try
{
    complaintDate = Convert.ToDateTime(s);
}
catch
{
    Response.Write("An error occurred while trying to convert the date!");
    return;
}

int totalDays = (int)(DateTime.Now - complaintDate).TotalDays;
if (totalDays >= 20)
{
    // Perform your actions here. 20+ days have elapsed.
}

Another solution in SQL Server is to put in a calculated field in the table that displays how many days lapsed. Will show Null if CompliantDate is null

ALTER TABLE ComplianceTable ADD
DaysLapsed  AS datediff(dd,CompliantDate,getdate())
GO