Calendar extender

Question

Hi guys, I am using calendarextender control in asp.net.It displays only date ,not time.But while inserting to database ,time is inserted automatically.But while retrieving the data from database using date in where condition ,no correponding records displays.What modification in storedprocedure i can do to resolve this problem.

I used following code in storedprocedure.Name Of table is FileM

Select * from FileM Where OldDate = '@OldDate%'

OldDate field in databese is stores as datetime i.e date is date i inserted but time is in either 08:00:00:000 or 00:00:00:000.So the above query gives records for date that coming with time as 00:00:00:000,but not with 08:00:00:000

Answer 1

-- assumes that the @OldDate parameter is a DATETIME
SELECT *
FROM FileM
WHERE DATEDIFF(day, OldDate, @OldDate) = 0

If you're able to modify the stored procedure then you can have more flexibility and performance by passing two DATETIME parameters, specifying the start and end times of the range that you need:

CREATE PROCEDURE dbo.GetRangeOfRecords
@StartDateTime DATETIME,
@EndDateTime DATETIME
AS
SELECT *
FROM FileM
WHERE OldDate BETWEEN @StartDateTime AND @EndDateTime

And then call your stored procedure like this, for example:

EXEC dbo.GetRangeOfRecords
    @StartDateTime = '2009-02-25 00:00:00.000',
    @EndDateTime = '2009-02-25 23:59:59.999'

Answer 2

The following code will strip out the time component of your datetime value (by strip out I mean make it 00:00:00 i.e. midnight): -

dateadd(d,datediff(d,0,MyDateTimeColumn),0)

so you can use: -

Select * from FileM Where dateadd(d,datediff(d,0,OldDate),0) = @OldDate

Alternatively, if you are using SQL Server 2008, there is a new date data type (with no time component).

Answer 3

As long as none of your dates are explicitly given a time component, they should all be defaulting to 00:00:00.000. In that case a simple equality comparison will do just fine. In your code though, you're putting quotes around your parameter, which means that you're now comparing your date to a literal string of "@OldDate%". Remove the quotes and see if that helps.

I like Luke's answer for flexibility, but if you don't have that option and can only correct the existing stored procedure, maybe this will help.