domster domster - 2 years ago 115
ASP.NET (C#) Question

Error Programmatically Converting Set DateTime

I have a method below to actually get the

count
from a table named
AuditActivity
whereby it takes the starting time of today and end time of today, 00:00:00 and 23:59:59 respectively and another condition. A
datetime
range.

For example, today's date 26/07/17, I want to specify in the
where
condition for the
DateTimeActivity
to take
count
only when it is
between
26/07/17 00:00:00 AND 26/07/17 23:59:59.

I know what I'm coding is actually wrong but I just seem to not be able to get it right.

I'm currently converting
nvarchar
to
datetime
which is wrong..

Extra information:
datatype
for DateTimeActivity is
datetime
in my
database


And an example of a value of the field as mentioned right above: '26/07/2017 22:43:06'

//Wrong
//Get Number of Logins today from AuditActivity
public int countLoginsToday()
{
int count = 0;

StringBuilder sqlCmd = new StringBuilder();
sqlCmd.AppendLine("SELECT COUNT(*) FROM AuditActivity WHERE DateTimeActivity BETWEEN @getStartOfDay AND @getEndOfDay AND ActivityType = @getType");

//Date Today
String todayDate = DateTime.Now.Date.ToString();

//Time Start
String timeStart = "00:00:00";

//Time End
String timeEnd = "23:59:59";

try
{
SqlConnection myConn = new SqlConnection(DBConnectionStr);

myConn.Open();

SqlCommand cmd = new SqlCommand(sqlCmd.ToString(), myConn);

cmd.Parameters.AddWithValue("@getStartOfDay", Convert.ToDateTime(todayDate + " " + timeStart));
cmd.Parameters.AddWithValue("@getEndOfDay", Convert.ToDateTime(todayDate + " " + timeEnd));
cmd.Parameters.AddWithValue("@getType", "Login");

count = Convert.ToInt16(cmd.ExecuteScalar());

myConn.Close();

return count;
}
catch (SqlException ex)
{
logManager log = new logManager();
log.addLog("AuditNLoggingDAO.getLoginsToday", sqlCmd.ToString(), ex);
return 0;
}
}


Error I'm getting:

enter image description here

Any help is much appreciated! Thanks!

Answer Source

You get the error because

todayDate + " " + timeStart

would end up as

26/07/17 00:00:00 00:00:00

Which is not a valid date time to be converted.

You can construct the times as follows

//Time Start
var timeStart = DateTime.Today; // 26/07/17 00:00:00

//Time End
var timeEnd = timeStart.AddDays(1).AddSeconds(-1); // 26/07/17 23:59:59

And add them to the query as is

cmd.Parameters.AddWithValue("@getStartOfDay", timeStart);
cmd.Parameters.AddWithValue("@getEndOfDay", timeEnd);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download