How to retrieve record from SQl using Date as parameter.

Question

in my table am storing one column(smalldatetime).

i need to retreive records by giving just date- and search the column specified above.

Ex:

10/6/2010 4:01:00 PM - this is the actual value in Column.

And i just want to search records from table by givin today's date. ..???

ALTER  PROCEDURE [dbo].[spDisplayAllOpenPrepaidSales]
@preStatus int,
@preCompanyId int,
@preCounterId int,
@preBillerId int,
 @today  smalldatetime 
AS 
BEGIN
print @today
select * from PrepaidSaleHeader 
where preOpenStatus = @preStatus And preCompanyId = @preCompanyId And 
preCounterId = @preCounterId And preEntryUserId = @preBillerId And preDate > @today
order by preDate
END

And pass parameter like;

spDisplayAllOpenPrepaidSales 0,2,4,2,'10/06/2010  00:00:00 AM'

Now this retreives record but gives me back - date in 24-hour Format.

Answer 1

The SQL DateDiff function is your friend - see SQL DataDiff Function

ALTER  PROCEDURE [dbo].[spDisplayAllOpenPrepaidSales]
@preStatus int,
@preCompanyId int,
@preCounterId int,
@preBillerId int,
@today  smalldatetime 
AS 
BEGIN
print @today
select * from PrepaidSaleHeader 
where preOpenStatus = @preStatus And preCompanyId = @preCompanyId And 
preCounterId = @preCounterId And preEntryUserId = @preBillerId 
And DateDiff(d,preDate,@today) = 0 --to find all dates matching the parameter, regardless of the time
order by preDate
END

Answer 2

I'm going to assume you mean you want to retrieve all records which match todays' date regardles of the time part? If so you can do something quite simply, but clumsy as this.

Get the Day of year for teh column, and the year for the column, and compare against eh day of year for the current date and the year for the current date (Or whatever date you are comparing against).

SELECT * FROM MyTable WHERE DATEPART(dy,MyColumn) = DATEPART(dy,GETDATE()) AND DATEPART(yy,MyColumn) = DATEPART(yy,GETDATE())

So your procedure would look like:

ALTER  PROCEDURE [dbo].[spDisplayAllOpenPrepaidSales]
@preStatus int,
@preCompanyId int,
@preCounterId int,
@preBillerId int,
 @today  smalldatetime 
AS 
BEGIN
print @today
select * from PrepaidSaleHeader 
where preOpenStatus = @preStatus And preCompanyId = @preCompanyId And 
preCounterId = @preCounterId And preEntryUserId = @preBillerId And DATEPART(dy,preDate) = DATEPART(dy,@today)) AND DATEPART(yy,preDate) = DATEPART(yy,@today)
order by preDate
END

Answer 3

You should use an ISO 8601 conformant format for the date, e.g.

spDisplayAllOpenPrepaidSales 0,2,4,2,'2010-10-06 00:00:00'

Times should be in 24hr form, not AM/PM form, so my local time now would be '13:02:00'. If you want all records for today, because the date value includes time, anything past the absolute beginning of today (00:00:00) is actually bigger that today. I always use the algorithm where myDate >= today and myDate < tomorrow, which translates to:

where preDate >= '2010-10-06' and preDate < '2010-10-07'

To achieve generality with this, use the DATEADD() function:

where preDate >= @today and preDate < DATEADD(day, 1, @today)