Yes. Stop using string concatenation to assemble SQL commands...
When use concatenate strings (ignoring the potential for SQL Injection attacks and the damage they can do to you database) you pass data to SQLCE in a format that it has to guess what you are talking about, and try to work out the date format. That probably isn't causing your problem here, but it's worth using parametrised queries anyway:
myCommand = "Select SiteCodeAndDate from Shifts where Date < @DT";
SqlCeConnection con = new SqlCeConnection(connect);
SqlCeCommand com = new SqlCeCommand(myCommand, con);
com.Parameters.AddWithValue("@DT", today);
Means that the date is passed in correctly and can never be misinterpreted.
Now, to the problem to are specifically meeting:
Why did you make the database field "Date" a text based column? Why didn't you make it DateTime? That way the comparison would have been between two DateTime objects instead of two strings. And string comparisons are based on looking at each character in turn until you find a difference and useng that as the whole comparison.
So, "date < today":
31/10/2013
30/10/2013 LOWER: '0' is lower than '1'
1/11/2013
30/10/2013 HIGHER: '1' is lower than '3'
See what I mean?