C# Casting vs. Parse

Question

I am building a user search and I have a Users table with id, first, and last in it.

I then query a names, say: "John Smith".

Using PHP I'll break the query into 2 parts: q1 = John and q2 = Smith.

I want to devise a points system where the record that has first = John and last = Smith gets pulled to the top.

So my sql is something like this:

 SELECT User.id, User.first, User.last
 FROM users as User
 WHERE (User.first = q1 OR User.first = q2) OR //if this is true +1 points
 WHERE (User.last = q1 OR User.last = q2) OR //if this is true +1 points

//then sum up the points and order by it (so the User.id with John Smith will have 2 points)

Any suggestions?

Solution 1

If g[0]["MyUntypedDateField"] is really a DateTime object, then the cast is the better choice. If it's not really a DateTime, then you have no choice but to use the Parse (you would get an InvalidCastException if you tried to use the cast)

Solution 2

Casting is the only good answer.

You have to remember, that ToString and Parse results are not always exact - there are cases, when you cannot safely roundtrip between those two functions.

The documentation of ToString says, it uses current thread culture settings. The documentation of Parse says, it also uses current thread culture settings (so far so good - they are using the same culture), but there is an explicit remark, that:

Formatting is influenced by properties of the current DateTimeFormatInfo object, which by default are derived from the Regional and Language Options item in Control Panel. One reason the Parse method can unexpectedly throw FormatException is if the current DateTimeFormatInfo.DateSeparator and DateTimeFormatInfo.TimeSeparator properties are set to the same value.

So depending on the users settings, the ToString/Parse code can and will unexpectedly fail...

Solution 3

Your code suggests that the variable may be either a date or a string that looks like a date. Dates you can simply return wit a cast, but strings must be parsed. Parsing comes with two caveats;

1. if you aren't certain this string can be parsed, then use DateTime.TryParse().

2. Always include a reference to the culture you want to parse as. ToShortDateString() returns different outputs in different places. You will almost certainly want to parse using the same culture. I suggest this function dealing with both situations;

   private DateTime ParseDateTime(object data)
   {
       if (data is DateTime)
       {
           // already a date-time.
           return (DateTime)data;
       }
       else if (data is string)
       {
           // it's a local-format string.
           string dateString = (string)data;
           DateTime parseResult;
           if (DateTime.TryParse(dateString, CultureInfo.CurrentCulture,
                                 DateTimeStyles.AssumeLocal, out parseResult))
           {
               return parseResult;
           }
           else
           {
               throw new ArgumentOutOfRangeException("data", 
                                  "could not parse this datetime:" + data);
           }
       }
       else
       {
           // it's neither a DateTime or a string; that's a problem.
           throw new ArgumentOutOfRangeException("data", 
                                 "could not understand data of this type");
       }
   }
   

Then call like this;

ParseDateTime(g[0]["MyUntypedDateField").ToShortDateString();

Note that bad data throws an exception, so you'll want to catch that.

Also; the 'as' operator does not work with the DateTime data type, as this only works with reference types, and DateTime is a value type.

Comments

[Sam Saffron]

sum (case when .... then 1 else 0 end) should do the trick

[cdub]

that'll work, but how do i stop the ones with 0 points from showing up in the result set?

[cdub]

that's what I thought but strangely enough i get this: Unknown column 'points' in 'where clause'

[vbence]

Oh, sorry, you have to use HAVING points > 0 in cases like this.