Hi Darryn,
I may not be much help here, because I don't even know what the "#"
signs actually mean. I don't think you want to send them to SQL. I'm
better at SQL than C#, but I do know both.
Anyway, my guess is that the date field you are querying on has a
non-zero time portion. So, for example, if you have a field like
this:
Transaction_Date
----------------
11/01/2004 10:30:45
Then, a query asking to return all records WHERE Transaction_Date =
'11/01/2004' will absolutely NOT return this record. Although, I
think some databases might allow you to setup your date/time defaults
to allow that behavior (I'm not sure about that part).
Anyway, this is a very common problem, and in practice, there are
several ways around this, and depending on your environment and
requirements, you can pick from these. There may be other options.
Let me know if you find a better one.
Option 1 - Eliminate the time portion from the comparison
- Oracle: WHERE TRUNC(Transaction_Date) =
TO_DATE('11/01/2004','MM/DD/YYYY')
- SQL Server: WHERE CONVERT(varchar,Transaction_Date,111) =
'2004/11/01'
I'm more familiar with the Oracle fix here, and have not yet been able
to find a better way in SQL Server. The 111 is a style parameter that
expects the YYYY/MM/DD format on the right side. There is a
corresponding format mask in C# .NET. You could also convert to a
date, but that may be too much overhead. Using the 111 allows a
simple string comparison to actually work because you send the year,
then the month, then the day, which ensures a proper comparison in
cases where a simple equals (i.e. =) comparison is not being used.
That is, you wouldn't want to compare a string in the form DD/MM/YYYY
to a >= operator. You'll get incorrect results.
You should note that both of these approaches will probably break
(i.e. not use) your indexes, potentially causing slower performance.
This would be a problem in a data-warehouse environment, but not in an
operational system. There are ways around that too, possibly
including function-based indexes. I haven't gotten around to trying
that too much.
Option 2 - Don't store the time portion OR Store it separately
Dates and times cannot usually be stored independently, at least not
in a 'datetime' field. That is, you usually need them both together.
A time can not be stored without it's date. From what I've read, if
you have a data-warehouse environment with lots of data where using an
index is important (and therefore option 1 would not really be smart),
then some people recommend storing the value in two separate fields.
One would be the date, with a 00:00:00 time (for searching), the other
would be the date and the actual time (for display). I have also seen
the time stored in a char(5) field. Either way would work.
Good luck and let me know if this helped or hurt you.
-Ryan
