rune711 rune711 - 1 month ago 6
C# Question

selecting from table when date and time are in separate columns

In sqlServer I have an older database that, for whatever reason, stored the dates and times in seperate rows of the table so that I have

ID | ACTDate | ACTTime | NOTE
1 | 2016-02-17 00:00:00.000 | 1432 | Sold 4 boxes.


ACTDate
is a
DateTime
, and
ACTTime
is a
char(4)
.

I want to be able to query the number of reports in the last X hours from my winForm app (in c#). So my query is something like:

DateTime startDate = DateTime.Now.AddHours(-numHoursToLookBack);
SELECT NOTE FROM myTable WHERE ACTDATE >= '" + startDate.ToString("yyyy-MM-dd HH:mm:ss.fff") + "' "


Obviously that does not work because the ACTDATE is always midnight on the day being queried.

So, I need a way to combine the columns during the query. I have researched a number of ways to CAST a column to a type during the query, but here it's like I need to combine them, then cast them, then select them based on the result. I am sorry but I really have no idea on where to begin with that.

Any help is appreciated!

Answer

Of course the correct action should be a one time script that unifies the date and the time, but, if this is not possible, then you should use two where conditions (and this could only work if your times are stored with leading zeros for hours less than 10)

 DateTime startDate = DateTime.Now.AddHours(-numHoursToLookBack);
 string initTime = startDate.ToString("hhmm");

 // This is just as an example because you haven't provided more context
 // but remember to never build query in this way. Use always parameters     
 SELECT NOTE FROM myTable WHERE ACTDATE >= '" + 
    startDate.ToString("yyyy-MM-dd") + "' AND ACTIME >= '" + 
    initTime + "'"

With parameters

 string query = @"SELECT NOTE FROM myTable 
                 WHERE ACTDATE >= @date 
                 AND ACTIME >= @time";
 SqlCommand cmd = new SqlCommand(query, connection);
 cmd.Parameters.Add("@date", SqlDbType.Date).Value = startDate;
 cmd.Parameters.Add("@time", SqlDbType.Char, 4).Value = initTime; 
 // Execute the command