I'm making a small blog app. I'm using a repeater control to display the news to the visitors. I have a setting in web.config that contains the default date range to display to the visitor when the page loads for the first time. I use a calendar control to
allow the visitor to select the time period they want to view, by day/week/month. Here's the details: news table in SQL Serve 2000: id (int) title(varchar, 75) content (text) dateEntered (datetime) stored proc: ---------------------------------------------------------------------------------
-- Stored procedure that will select all rows between two dates -- or all rows with a given date -- Returns: @iErrorCode int --------------------------------------------------------------------------------- ALTER PROCEDURE dbo.pr_news_SelectRange @fromDate
DateTime, @toDate DateTime, @iErrorCode int OUTPUT AS -- SELECT all rows from the table. IF @fromDate = @toDate BEGIN SELECT * FROM [dbo].[news] WHERE dateEntered = @fromDate ORDER BY [dateEntered] DESC END ELSE SELECT * FROM [dbo].[news] WHERE dateEntered
BETWEEN @fromDate AND @toDate ORDER BY [dateEntered] DESC -- Get the Error Code for the statement just executed. SELECT @iErrorCode=@@ERROR --------------------------------------------------------------------------------- Here's my problem. When a visitor
selects a month or day, the news is displayed correctly. However, when a visitor selects a single day, nothing gets displayed. I know there are news posts for the date selected. What I suspect the problem to be is that the date being passed to the stored proc
doesn't have a time associated with it, as it's a day taken from the selectedDay property on the calendar control. Anyone have any insight for this problem? I've read the "create your own weblog" article on msdn... and have followed many of it's processes.
Was hoping others have experienced this and can aid me. Thanks in advance. Chad
When selecting a data range, you do need to be careful when the data is stored with the TIME as part of the number. for example, to select todays date, you cant ask for all records with a datestamp equal to today. you need to ask for all records within the
full 24 hour range. Here's the psuedo code: Datestamp >= Date() AND DateStamp < Date() + 1 this gives you all date values for the full 24 hour period
I also just noticed that your BETWEEN clause is probably not working correctly either. Does the data that gets returned actually contain any records that were stamped on @toDate ? I would guess it wouldnt if the dates are stored with time values. You are also
going to need to push out that end date too to get the full 24 hour range. You should be able to use just one select statement instead of 2.
Thanks! You mentioned something that tipped off something in my head. lol... I forgot to add a day to my toDate (in my dataTier class file). It's all working now. Appreciate the help! =D Chad
ChadThiele
Participant
983 Points
274 Posts
Problems displaying data by date
Jan 29, 2004 10:01 AM|LINK
mbanavige
All-Star
135167 Points
15505 Posts
ASPInsiders
Moderator
MVP
Re: Problems displaying data by date
Jan 29, 2004 12:16 PM|LINK
mbanavige
All-Star
135167 Points
15505 Posts
ASPInsiders
Moderator
MVP
Re: Problems displaying data by date
Jan 29, 2004 12:20 PM|LINK
ChadThiele
Participant
983 Points
274 Posts
Re: Problems displaying data by date
Jan 29, 2004 01:00 PM|LINK