September 19, 2005 at 10:24 am
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
September 19, 2005 at 10:37 am
Oh and one more thing you shouldn't be using dynamic SQL when you don't really need it!
* Noel
September 19, 2005 at 10:50 am
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
September 19, 2005 at 11:01 am
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
September 19, 2005 at 11:14 am
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
September 19, 2005 at 11:27 am
You may want to read this before going down that road :
September 19, 2005 at 11:30 am
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
September 19, 2005 at 7:12 pm
September 20, 2005 at 6:49 am
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