CODEWITHSUNDEEP
c#asp.net
Roshan
Roshan

Reputation: 49

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

`

Upvotes: 0

Views: 687

Answers (3)

Devin Burke
Devin Burke

Reputation: 13820

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.
}

Upvotes: 3

Justin King
Justin King

Reputation: 1428

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

Upvotes: 0

rsbarro
rsbarro

Reputation: 27339

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);

Upvotes: 3

Related Questions