date between query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

date between query

Author

Topic

ramesh.g
Starting Member

11 Posts

Posted - 2010-10-05 : 06:34:39

MY stored procedure is below which work properly

ALTER PROCEDURE [dbo].[placement_pundit_advance_search] --'UploadDate between ''01/01/1900 00:00:00'' and ''5/10/2010 00:00:00'''
@SearchCriteria varchar(525)
AS
declare @sql varchar(max)
BEGIN
set @sql = 'SELECT jr.BackGround,Convert(datetime, jr.UploadDate,103) as UploadDate ,jr.Career,jr.Credentials,jr.CVDetails,jr.Email,jr.JSPerID,jr.JSProfID,jr.Phone,jr.Suitability,Convert(datetime,UploadDate,103) as UploadDate,bk.point2,bk.point1,bk.point3 FROM JobSeeker_Professional jr
Inner Join background bk On jr.BackGround=bk.backid where ' + @SearchCriteria
exec(@sql)
END

but when i execute it through browser it gives error below

An expression of non-boolean type specified in a context where a condition is expected, near 'UploadDate between ''01/01/1900 00:00:00'' and ''05/10/2010 00:00:00'' '.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An expression of non-boolean type specified in a context where a condition is expected, near 'UploadDate between ''01/01/1900 00:00:00'' and ''05/10/2010 00:00:00'' '.

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 06:46:31

Replace exec(@sql) with print(@sql) and show us what it prints.

PBUH

ramesh.g
Starting Member

11 Posts

Posted - 2010-10-05 : 07:09:58

Thanks sachin .
it print query like this

SELECT jr.BackGround,Convert(datetime, jr.UploadDate,103) as UploadDate ,jr.Career,jr.Credentials,jr.CVDetails,jr.Email,jr.JSPerID,jr.JSProfID,jr.Phone,jr.Suitability,Convert(datetime,UploadDate,103) as UploadDate,bk.point2,bk.point1,bk.point3 FROM JobSeeker_Professional jr
Inner Join background bk On jr.BackGround=bk.backid where UploadDate between '01/01/1900 00:00:00' and '5/10/2010 00:00:00'

and when i execute it like below

[placement_pundit_advance_search] 'UploadDate between ''01/01/1900 00:00:00'' and ''5/10/2010 00:00:00'''

it gives result also
then why it showing below error in browser

An expression of non-boolean type specified in a context where a condition is expected, near 'UploadDate between ''01/01/1900 00:00:00'' and ''05/10/2010 00:00:00'' '.

please help me.

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 07:23:00

I have no idea.Seems more like a .net issue than SQL one.Just shot in dark.
Change the parameter to this


'UploadDate between '''01/01/1900 00:00:00''' and '''5/10/2010 00:00:00''''

PBUH

ramesh.g
Starting Member

11 Posts

Posted - 2010-10-05 : 07:28:53

no it gives below error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '01'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-10-05 : 10:09:32

How are you passing values thru your front end?

Madhivanan

Failing to plan is Planning to fail

ramesh.g
Starting Member

11 Posts

Posted - 2010-10-05 : 10:32:58

i execute it through by passing parameter to my stored procedure as below (placement_pundit_advance_search is name of my stored procedure and in single quote i have given parameter)

placement_pundit_advance_search 'UploadDate between ''01/01/1900 00:00:00'' and ''5/10/2010 00:00:00'''

Sachin.Nand

2937 Posts

Posted - 2010-10-05 : 13:21:38

quote:
Originally posted by ramesh.g

no it gives below error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '01'.

No I mean change it in your .net code.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources