On Fri, 07 Jan 2005 04:16:28 -0000, Marc Pelletier wrote:
I have a table with a Day field, defined as smalldatetime. I am filling it
from a CSharp application with the following code:
DataRow r = dtStaDays.NewRow();
r["Station_ID"]= station_ID;
r["Day"] = sd.Date;
r["Range"] = rangeTide;
etc.
However when I do a query using
"Select * FROM StationDays where Station_ID = 8 and Day = 06/01/2005"
I don't get any results even though that row exists. To make it work I have
to use
"Select * FROM StationDays where Station_ID = 8 and Day > 06/01/2005"
(which isn't very satisfactory
or
SELECT * FROM StationDays WHERE (Station_ID = 8) AND ([Day]
= CONVERT(DATETIME, '2005-01-06 00:00:00', 102))
What can I do to simplify this query?
Hi Marc,
The reason is that SQL Server has no datatype to store date only - the
datetime datatype stores both date and time. Your date constant 06/01/2005
(which is an ambiguous date format, by the way - I suggest you to only use
the unambiguous format YYYYMMDD, without seperators) will be converted to
a datetime value with the time portion equal to 00:00:00.000. However, the
values in the column Day are filled by your CSharp app (sd.Date) and
probably do hold a time portion. And of course, '20050106 13:40:57.530' is
not equal to '20050161 00:00:00.000'.
There are basically two ways to get around this. One os to change your
WHERE clauses. Not the way you have them above (the first version will
also return rows with a date after 06/01/2005 and the second will not be
able to use any index on the Day column). You'll have to change your logic
from "I want all rows with Day equal to 06/01/2005" to "I want all rows
with Day+Time from midnight 06/01/2005 up to but not including midnight
07/01/2005". Or, in SQL and using unambiguous date format:
WHERE Day >= '20050106'
AND Day < '20050107'
The other way is to make sure that the values in the Day column are
stripped of their time portion (or rather: that they all have the default
time portion 00:00:00.000). Since the values are filled in a CSharp
application, your best bet would be to investigate the builtin functions
of CSharp for this. Another way would be to do the inserts and updates
through a stored procedure that removes the time portion from the supplied
parameter before inserting or updating the row. If you can't use CSharp to
remove the time portion AND you can't use stored procedures to tidy up the
data, the last resort is to use a trigger - but only use this as a last
resort, since I don't really like the concept of inserting "dirty" data
that gets tidied up under the covers.
If you use a stored procedure (or a trigger) to remove the time portion,
you can use the expression below. The trick used is to calculate the
number of days between a fixed date (any date will do) and the datetime
supplied, than add this number of days to the same fixed date. In the
example below, I use CURRENT_TIMESTAMP - replace this with the name of
your datetime column or datetime parameter.
SELECT DATEADD(day,
DATEDIFF(day, '20040101', CURRENT_TIMESTAMP),
'20040101')
You can use the same expression if you need to remove the time part from
the data already present in your table:
UPDATE MyTable
SET MyDate = DATEADD(day,
DATEDIFF(day, '20040101', MyDate),
'20040101')
Don't forget to check out Tibor Karaszi's artivel about the datetime
datatype:
http://www.karaszi.com/SQLServer/info_datetime.asp.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
