news:4EC2522F-EE60-4FA8-AF95-C120A493C6FB@microsoft.com:

> 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
>
>
>

"Daniel Faensen" wrote:

> 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
>
> =?Utf-8?B?UEs5?= <P**@discussions.microsoft.com> wrote in
> news:4EC2522F-EE60-4FA8-AF95-C120A493C6FB@microsoft.com:
>
> > 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
> >
> >
> >
>
>