Need Help w/ "syntax error converting datetime from character stri

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm getting the following error and I'm not sure how to remedy: "syntax error
converting datetime from character string".
I have a .NET application that is accessing a SQL Server database. I've
created a stored procedure that takes two dates as inputs and does a SELECT
based on those dates. Right now I'm passing in the dates from the
application side as SQLParameters of the type SQLDBType.DateTime (see below
for example). I've tried the convert function in SQL Server but I'm not sure
if I'm using it correctly or if I need to for that matter.

I'll give a quick example of what I'm doing.
Application Side (adding date as parameter):
prmKeyRequest[0] = new SqlParameter();
prmKeyRequest[0].ParameterName = "@ToDate";
prmKeyRequest[0].Direction = ParameterDirection.Input;
prmKeyRequest0].SqlDbType = SqlDbType.DateTime;
prmKeyRequest[0].Size = 8;
prmKeyRequest[0].Value = ToDate;


SQL SERVER 2000 Strored Procedure:
CREATE PROCEDURE cmw_keyrequest_sel_transactionsbydate
@FromDate nvarchar,
@ToDate nvarchar


AS
DECLARE @DynamicSql nvarchar(250)

SET @DynamicSql = 'Select dmh.NationalShortTitle,
dmh.ReceivingAccountNumber, dmh.Qty
From DADIMessageOutHistory dmh
Where
dmh.LastUpdate >= ' + @FromDate + ' AND LastUpdate <= ' + @ToDate

Exec
(
@DynamicSql
)
GO
 
First, I think you should declare the procedure's parameters as DATETIME:

CREATE PROCEDURE cmw_keyrequest_sel_transactionsbydate
@FromDate DATETIME,
@ToDate DATETIME

Second, try a parameterized SQL statement instead of dynamic SQL. This
improves performance:

Select dmh.NationalShortTitle,
dmh.ReceivingAccountNumber, dmh.Qty
From DADIMessageOutHistory dmh
Where
dmh.LastUpdate >= @FromDate AND LastUpdate <= @ToDate

(BTW:
"LastUpdate BETWEEN @FromDate AND @ToDate"
is easier to read)

However, if you really like the dynamic SQL you can build the query
string as follows:

SET @DynamicSql = N'Select dmh.NationalShortTitle,
dmh.ReceivingAccountNumber, dmh.Qty
From DADIMessageOutHistory dmh
Where
dmh.LastUpdate >= ' + CONVERT(NVARCHAR(20),@FromDate) + ' AND
LastUpdate <= ' + CONVERT(NVARCHAR(20),@ToDate)

Daniel Faensen
 
Thanks Daniel! That helped a lot. I've got it now

Daniel Faensen said:
First, I think you should declare the procedure's parameters as DATETIME:

CREATE PROCEDURE cmw_keyrequest_sel_transactionsbydate
@FromDate DATETIME,
@ToDate DATETIME

Second, try a parameterized SQL statement instead of dynamic SQL. This
improves performance:

Select dmh.NationalShortTitle,
dmh.ReceivingAccountNumber, dmh.Qty
From DADIMessageOutHistory dmh
Where
dmh.LastUpdate >= @FromDate AND LastUpdate <= @ToDate

(BTW:
"LastUpdate BETWEEN @FromDate AND @ToDate"
is easier to read)

However, if you really like the dynamic SQL you can build the query
string as follows:

SET @DynamicSql = N'Select dmh.NationalShortTitle,
dmh.ReceivingAccountNumber, dmh.Qty
From DADIMessageOutHistory dmh
Where
dmh.LastUpdate >= ' + CONVERT(NVARCHAR(20),@FromDate) + ' AND
LastUpdate <= ' + CONVERT(NVARCHAR(20),@ToDate)

Daniel Faensen

I'm getting the following error and I'm not sure how to remedy:
"syntax error converting datetime from character string".
I have a .NET application that is accessing a SQL Server database.
I've created a stored procedure that takes two dates as inputs and
does a SELECT based on those dates. Right now I'm passing in the
dates from the application side as SQLParameters of the type
SQLDBType.DateTime (see below for example). I've tried the convert
function in SQL Server but I'm not sure if I'm using it correctly or
if I need to for that matter.

I'll give a quick example of what I'm doing.
Application Side (adding date as parameter):
prmKeyRequest[0] = new SqlParameter();
prmKeyRequest[0].ParameterName = "@ToDate";
prmKeyRequest[0].Direction = ParameterDirection.Input;
prmKeyRequest0].SqlDbType = SqlDbType.DateTime;
prmKeyRequest[0].Size = 8;
prmKeyRequest[0].Value = ToDate;


SQL SERVER 2000 Strored Procedure:
CREATE PROCEDURE cmw_keyrequest_sel_transactionsbydate
@FromDate nvarchar,
@ToDate nvarchar


AS
DECLARE @DynamicSql nvarchar(250)

SET @DynamicSql = 'Select dmh.NationalShortTitle,
dmh.ReceivingAccountNumber, dmh.Qty
From DADIMessageOutHistory dmh
Where
dmh.LastUpdate >= ' + @FromDate + ' AND LastUpdate <= ' + @ToDate

Exec
(
@DynamicSql
)
GO
Click to expand...
Click to expand...
 
Back
Top