Click here to Skip to main content
12,349,778 members (26,161 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: stored-procedure
My stored procedure is as follows,

 -- Add the parameters for the stored procedure here
@FromDate datetime,
@ToDate datetime
 
    --Select query
   DECLARE @query nvarchar(max)
 
   set @query='SELECT [col1]
               FROM [Table1]
               WHERE ([col2] BETWEEN '''+@FromDate+''' AND'''+@ToDate+''')'
 
    execute sp_executesql @query

Executing this string query results in following error,

"Conversion failed when converting date and/or time from character string"

Any one please help me to sort this out
Posted 7-Apr-13 5:09am
Edited 7-Apr-13 5:18am
v2
Comments
ThePhantomUpvoter 7-Apr-13 11:38am
   
Post the code that you have for calling this stored procedure.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Your query looks OK...

Try this:
set @query=N'SELECT [col1] 
           FROM [Table1] 
           WHERE ([col2] BETWEEN ''' + @FromDate + ''' AND ''' + @ToDate + ''')'

Check the input parameters by printing its content to MS SQL Managment Studio - Messages window:
PRINT @FromDate
PRINT @FromDate
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Hi, You can try this

@FromDate datetime,
 @ToDate datetime
 
     --Select query
    DECLARE @query nvarchar(max)
 
    set @query='SELECT [col1]
                FROM [Table1]
                WHERE ([col2] BETWEEN '''+CONVERT(NVARCHAR, @FromDate,21)+''' AND'''+CONVERT(NVARCHAR, @ToDate, 21)+''')'
 
     execute sp_executesql @query
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 8 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100