Retrieve data of a datetime field based on FromDate and ToDate

  • Trying to retrieve data from SQL2k based on a datatime field in stored procedure. In the SP FromDate & ToDate passed as a parameter from the from calling module but when it runs it gives error "Syntax error converting datetime from character string." here is the SP code.

     

    CREATE           PROCEDURE usp_ReturnMessage

     @ComID INT,

     @PracticeName VARCHAR(100),

     @AgencyNo VARCHAR(20),

     @FromDate DATETIME,

     @ToDate DATETIME,

     @CurrentPage INT,

     @PageSize INT,

     @TotalRecords INT OUTPUT,

     @ErrorCode INT OUTPUT,

     @ErrorMessage VARCHAR(100) OUTPUT

    AS

    SET NOCOUNT ON

    SET DATEFORMAT DMY

    DECLARE @FirstRecord INT, @LastRecord INT

    SELECT @FirstRecord = (@CurrentPage - 1)* @PageSize

    SELECT @LastRecord = (@CurrentPage* @PageSize + 1)

    CREATE TABLE #TempMessage

    (

     ID INT IDENTITY,

     CompID INT,

     gencyNo VARCHAR(20),

     Error VARCHAR(1000)

    )

    DECLARE @strSQL VARCHAR(500)

    SET @strSQL='SELECT p.ID_i,PracticeNo_vc,Error_vc FROM tbl_Message m,tbl_Company p WHERE p.ID_i=m.ID_i AND m.ID_i='+ CONVERT(VARCHAR(10),@ComID)

    SET @strSQL= @strSQL + ' AND SubmissionDate_dt BETWEEN '  + @FromDate + ' AND ' + @ToDate

    --SubmissionDate_dt is a datetime field in tbl_message table

    IF @PracticeName IS NOT NULL

    SET @strSQL = @strSQL + ' AND DTPUserName_vc LIKE ''%' + @PracticeName +'%'''

    SET @strSQL=@strSQL + ' ORDER BY DTPUserName_vc'

    INSERT INTO #TempMessage(ID,AgencyNo,Error)

    EXEC (@strSQL)

     SELECT *, MoreRecords = (SELECT COUNT(*) FROM #TempMessage TempPD WHERE TempPD.ID >= @LastRecord)

     FROM #TempMessage

     WHERE ID > @FirstRecord AND ID < @LastRecord

     SELECT @TotalRecords = COUNT(*) FROM #TempMessage

     SET NOCOUNT OFF

    IF @@Error =0

     BEGIN

      SET @errorcode=0

      RETURN

     END 

    ELSE

     BEGIN

      SET @errorcode=1

      SET @ErrorMessage='Error in retrieving XML message.'

      RETURN

     END 

    Would much appreciate your help

    Thank you 

  • SET @strSQL= @strSQL + ' AND SubmissionDate_dt BETWEEN '''  + convert( varchar(20),@FromDate 120) + ''' AND ''' + convert( varchar(20), @ToDate,120) +''''

     


    * Noel

  • Oh and one more thing you shouldn't be using dynamic SQL when you don't really need it!

     


    * Noel

  • When I modify this as above it gives me following error

    "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

    I need to write here a dynamic sql based on the parameter passed in case of non dynamic sql string it executes perfectly fine. It should retrieve data on the given date range fromdate and todate.

    Thanks once again for ur help

  • can you post what

    select  convert( varchar(20),@FromDate 120),  convert( varchar(20),@ToDate 120)

    Returns?

    2.

    No need for Dynamic ex:

    SELECT p.ID_i,PracticeNo_vc,Error_vc

    FROM tbl_Message m

         join

         tbl_Company p on p.ID_i= m.ID_i AND m.ID_i= @ComID

    WHERE

      SubmissionDate_dt BETWEEN  @FromDate AND  @ToDate

      AND  (DTPUserName_vc LIKE '%' + @PracticeName + '%' OR @PracticeName IS NULL)

      

    ORDER BY DTPUserName_vc


    * Noel

  • It returns yyyy-mm-dd hh:mi:ss(24h) format

    2005-08-18 00:00:00 formdate  2005-09-19 00:00:00 todate

    I have posted a part of sp here and there are other clauses as well so I need to make it a dynamic sql.

    Thanks

     

  • You may want to read this before going down that road :

    Dynamic Search Conditions in T-SQL

  • Did you included the SINGLE quotes as they were written? (no space between them)

    Can you PRINT what @sql variable looks like, just before the EXEC(@sql)?

    using Print @sql

     


    * Noel

  • SET DATEFORMAT YMD

    would help.

     

    _____________
    Code for TallyGenerator

  • And if it wasn't for the dynamic sql part, we'd all had been done 8 posts ago. Seriously, why do you HAVE TO use dynamic sql??????

Viewing 10 posts - 1 through 9 (of 9 total)

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