1 Replies - 11534 Views - Last Post: 24 May 2005 - 11:37 AM Rate Topic: -----

#1 sontek  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 8
  • View blog
  • Posts: 283
  • Joined: 13-September 01

ASP.net Calendar + SQL DateTime

Posted 24 May 2005 - 08:15 AM

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;

Is This A Good Question/Topic? 0
  • +

Replies To: ASP.net Calendar + SQL DateTime

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: ASP.net Calendar + SQL DateTime

Posted 24 May 2005 - 11:37 AM

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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1