[sontek]

Inside my database I have a DateTime with the layout "mm/dd/yyyy hh:mm:ss AM/PM" and then on a selected date of a calendar control it returns the date with 12:00:00 AM which obviously doesn't match with my database's time.

So, I'm looking for a way to drop the time off the database date when running a select statement. Here are the ways I've tried:

SELECT * from tblNews where CONVERT(Char(9), dtPostDate, 101) = '5/21/2005';

SELECT * from tblNews where CONVERT(SmallDateTime, dtPostDate, 101) = '5/21/2005';


But it seems that it still compares the time to 12:00:00 AM no matter what you do to it. So the only able I was able to get this to work was use the BETWEEN method.

SELECT * FROM tblNews WHERE (dtPostDate BETWEEN '5/21/2005' AND '5/22/2005') ORDER BY dtPostDate;

[Amadeus]

Is the time parameter important? If not, I suggest you modify your date parameters to store and retreive short dates.

In either case, the problem has to do with how the data is stored...if the time is important to you, but not for this query, then you can strip the fractional component off

SELECT * from tblNews where CAST(FLOOR(CAST(dtPostDate AS float)) AS datetime) = '5/21/2005';

Performance wise, however, the approved method is to use the between clause.

I've also taken the liberty of moving this to the database forum