Datetime Query

i am trying to query a datetime column in a db.

e.g. 3/7/2005 4:24:01 AM
My query is below :-
--
select a.date, b.useruri as 'FROM', c.useruri as 'TO',
a.body as 'MESSAGE' from messages as a
inner join
users as b
on a.fromid = b.userid
inner join
users as c
on a.toid = c.userid
where a.date like '%2005-03-01%'
order by a.dateSpecify the times using BETWEEN. Otherwise, you won't use any indexes and this will be extremely slow.|||Do i specify the date as a i wrote in the query. since the datetime is like
3/7/2005 4:24:01 AM ?

or do i have to declare the datime if it was today and use the variable in the query.|||Well, it depends what you're trying to achieve. :) If the data was stored like that, then just go from 00:00:00 to 23:59:59. If it was stored with more accuracy, it can get a little tricky. Note the following code results followed by an excert from Books Online:

CODE:

DECLARE @.dates TABLE(date1 DATETIME)

INSERT @.dates(date1)
SELECT '01/01/05 13:58:01.000' UNION ALL
SELECT '01/02/05 00:00:00.000' UNION ALL
SELECT '01/02/05 00:00:00.001' UNION ALL
SELECT '01/02/05 23:59:59.999' UNION ALL
SELECT '01/03/05 00:00:00.000' UNION ALL
SELECT '01/03/05 00:00:00.001' UNION ALL
SELECT '01/04/05 10:00:00.001'

SELECT date1 FROM @.dates

SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.999'

SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.997'

BOL Quote:

Date and time data types for representing date and time of day.

datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998 1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990

Microsoft SQL Server rejects all values it cannot recognize as dates between 1753 and 9999.sql