Click here to Skip to main content
11,504,689 members (70,690 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Stored Procedures
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 at 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
0 F-ES Sitecore 220
1 CHill60 195
2 OriginalGriff 185
3 Sergey Alexandrovich Kryukov 163
4 Kornfeld Eliyahu Peter 140
0 Sergey Alexandrovich Kryukov 673
1 Maciej Los 455
2 DamithSL 435
3 Peter Leow 430
4 OriginalGriff 417


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 8 Apr 2013
Copyright © CodeProject, 1999-2015
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