search date in stored procedure

Question

Post reply

search date in stored procedure

benkraiemchedlia

SSCommitted

Points: 1672
More actions
October 11, 2016 at 7:03 am

#318135

Hi,
I have a stored procedure, with datetime parameter, but when I enter a date ('2016-10-11') and I execute it doesn't work,
is there a way to convert @DateReceived to nvarchar because I want to search this date from asp:textbox
this is my code :
/****** Object: StoredProcedure [dbo].[GetApprovedReassessment] Script Date: 10/11/2016 2:05:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetApprovedReassessment]
@DateReceived datetime = NULL,
@ReassessmentStatus nvarchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 500
,[RegionalProjectManager]
,[ReassessmentRejectionReason]
,[ReassessmentStatus]
,[DateReceived]
FROM [dbo].[Reassessments]
WHERE
([DateReceived] = @DateReceived OR @DateReceived IS NULL) AND
([ReassessmentStatus] = @ReassessmentStatus OR @ReassessmentStatus IS NULL)
ORDER BY [DateReceived] desc
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why do you think that converting to nvarchar will make things better?

What type is DateReceived in Reassessments table?

Are you storing time in DateReceived? '2016-10-11' is not the same as '2016-10-11 10:23:35'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 2

SQL will do an implicit conversion to a DATETIME data type.

For Example:

DECLARE @myDateTime DATETIME = '2016-10-11'

SELECT @myDateTime

Will get you:

2016-10-11 00:00:00.000

What does you data look like?

You might want to do something like this...

SELECT myColumns FROM myTable WHERE myDateTime >= @myParameter AND myDateTime < DATEADD(dd, 1, @myParameter)

It may seem strange but you don't want to do conversions in your WHERE statement because you'll render your indexes unusable (non sargable).

SELECT quote FROM brain WHERE original = 1
0 rows returned