Is 9:00 > 08:30 true or false?

cJeffreywang · February 1st, 2008, 10:00 AM

I have used a query statement with the following WHERE string to 'Fill' a dataset.

"AND (A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _

and strApptPreTime is defined as:
Dim strApptPreTime As String = myaspx.strPadTime(CStr(dApptCalcNewDate.Hour) + ":" + CStr(dApptCalcNewDate.Minute))

Somehow, the dataset showed only the ApptsTime after 10 am. After more than 2 weeks of debugging, I still can see a dataset watch for > 10 am AppsTime only. Now I am guessting, the problem is the 9 am data in SQL Server database are saved as 9:00, 9:10, etc, while the strPreApptTime is e.g. 08:30. Then I checked the data type settings for these variables. Here are what I found:

In SQL Server Agent job, the ApptsTime data was 'inserted' by @NewApptTimes, which is declared as char(5).
In SQL Server database, the ApptsTime was defined as nvarchar(15).

My question are:
     1. The reason why there were no 9 am data for the dataset, is becasue the WHERE string 9:00 > 08:30 is false?
     2. If the answer to the quation 1 is yes, how do I define AppsTime and/or strApptPreTime, so that 9:00 > 08:30 is true?
     3. I changed the ApptsTime data type to char(5), the SQL Server table still showed 9:00, 9:10, etc., why?

TIA,
Jeffrey

planoie · February 1st, 2008, 10:35 AM

Why are you not storing this data as the appropriate type?? (I.e. DATETIME) It seems like you are having lots of problems with dates and time but you are treating them as strings.

-Peter

cJeffreywang · February 1st, 2008, 12:19 PM

I changed the line for adding "0" into sHourTime.Length=1
to adding " ". The 9 am appointments now can be displayed
in the asspx pages. Now 9:00 > 8:40

planoie · February 1st, 2008, 12:28 PM

Ok... however... what happens when you test this:

9:30 > 10:00

A text comparison of that will result in true when a datetime comparison will result in false.

You still need to change your data types to work with the appropriate value types otherwise you are going to encounter more problems.

-Peter

cJeffreywang · February 2nd, 2008, 12:03 AM

(A.ApptsDate > '" & strApptPreDate & "' OR (A.ApptsDate = '" & strApptPreDate & "' AND A.ApptsTime >= '" & strApptPreTime & "' ))" & _

9:00 > 8:40 is just an example to demostrate that ApptsTime > strApptPreTime is working now. Previously, at 8:40 am, it was
9:00 > 08:40 and this Where statement is 'false' so the 9 am appointments data were not display in the dataset table of the aspx page.

At 10:00, of course, 9:00 > 10:00 is false and the 9 am appointments data will be removed from the dataset table.

planoie · February 3rd, 2008, 12:16 AM

Jeffrey,

Between this and the other thread addressing this issue, I feel like my suggestions have been falling on deaf ears. You have yet to respond to my inquiries as to why you are storing date values as strings instead of the appropriate DateTime data type. Perhaps this sample and explanation will help to convince you that the problems you are having are the result of an incorrect choice in data type usage.

Consider the following code:

Code:

string[] strTimes = new string[] { "8:30", "08:40", "9:00", "10:00" };
for(int i = 0; i < strTimes.Length - 1; i++)
{
  Console.Write("{0} <-> {1}:", strTimes[i], strTimes[i+1]);
  Console.Write("[string: {0}]", string.Compare(strTimes[i], strTimes[i + 1]));
  Console.Write("[date: {0}]", DateTime.Compare(DateTime.Parse(strTimes[i]), DateTime.Parse(strTimes[i + 1])));
  Console.WriteLine();
}

When I run this, I get the following output:

Code:

8:30 <-> 08:40:[string: 1][date: -1]
08:40 <-> 9:00:[string: -1][date: -1]
9:00 <-> 10:00:[string: 1][date: -1]

Explanation of actual comparison results:
8:30 <-> 08:40
  - As strings: first is greater (incorrect)
  - As datetimes: second is greater (correct)
08:40 <-> 9:00
  - As strings: second is greater (correct)
  - As datetimes: second is greater (correct)
9:00 <-> 10:00
  - As strings: second is greater (correct)
  - As datetimes: first is greater (incorrect)

Why the incorrect ones?
8:30 <-> 08:40
  - As strings, '0' comes before '8'.
  - As datetimes, clearly 8:30 is before 8:40
9:00 <-> 10:00
  - As strings: '1' comes before '9'
  - As datetimes: 9:00 comes before 10:00

What this all means is that doing a comparison between strings and dates will not yield the same result. This means that your query and/or code that uses string comparisons will have unexpected results.

It bothers me to see someone spending any amount of time trying to debug a problem that shouldn't even exist. The simple fact is that you need to change the data type you are using for these values otherwise you are going to have problems.

I am confident that if you change your data types, all your comparison problems will disappear.

-Peter