I'm Getting : "syntax error converting datetime from character str

[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

 

[Miha Markic [MVP C#]]

Hi,

Why do you define both date parameters as nvarchar?
@FromDate nvarchar,
@ToDate nvarchar
You are passing DateTime type from .net and sql server awaits a string
type...

 

[Ritesh Jain via DotNetMonster.com]

Hi,

Just Try to Replace
@FromDate nvarchar,
@ToDate nvarchar

With

@FromDate nvarchar(25),
@ToDate nvarchar(25)

I hope This will Work...................

Regards,
Ritesh

 

[Guest]

I'm sorry, I pasted the incorrect declaration. I originally declared the sql
server variables as follows:
@FromDate datetime,
@ToDate datetime

I've been trying all kinds of combinations to try to get this to work. With
this in mind, do you have any advice?

Hi,

Why do you define both date parameters as nvarchar?
@FromDate nvarchar,
@ToDate nvarchar
You are passing DateTime type from .net and sql server awaits a string
type...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.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

 

[Ritesh Jain via DotNetMonster.com]

Hi Miha,
I Tried the Same way passing "Now" Value to the Stored Proc. Which Whose Parameter i defined as nvarchar..................

Intrestingly it did't give me any error.what could be the Reason is??

But just with nvarchar i.e w/o size it considers only first char of String but when i gave Size i.e nvarchar(25) it works fine!!!!!!!

Regards,
Ritesh

 

[Miha Markic [MVP C#]]

What type is ToDate of?
It should be DateTime.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

I'm sorry, I pasted the incorrect declaration. I originally declared the
sql
server variables as follows:
@FromDate datetime,
@ToDate datetime

I've been trying all kinds of combinations to try to get this to work.
With
this in mind, do you have any advice?

Hi,

Why do you define both date parameters as nvarchar?
@FromDate nvarchar,
@ToDate nvarchar
You are passing DateTime type from .net and sql server awaits a string
type...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.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

 

[Guest]

I've tried both ways. What is the correct way? I'm simply having a user
enter a date in a textbox in the application, then I'm passing it to the
stored proc. Shoud I do it as a datetime or string? Either way, I haven't
been able to get it to work yet

Hi,

Why do you define both date parameters as nvarchar?
@FromDate nvarchar,
@ToDate nvarchar
You are passing DateTime type from .net and sql server awaits a string
type...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.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

 

[Ritesh Jain via DotNetMonster.com]

Hi,
Did u verified that User is entering Valid DataTime???

 

[Ritesh Jain via DotNetMonster.com]

If Data is coming form TextBox than u can Simply Pass the Value with SQLDBType.nvarchar instead of DataTime......and specify the Lengh of Parameter in ur Stored Proc...........

 

[Guest]

This suggestion doesn't work "functionally". It doesn't bring back the
correct date range when I test my stored procedure through the query
analyzer.
eg. I'm assuming this is what you're suggesting..... But it doesn't
evaluate the dates correctly:

CREATE PROCEDURE cmw_keyrequest_sel_transactionsbydate
@FromDate nvarchar(25),
@ToDate nvarchar(25)

AS
DECLARE @DynamicSql nvarchar(500)

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

Exec
(
@DynamicSql
)
GO

 

[Guest]

It is of type DateTime on the .NET side after I convert it. Here is what I'm
doing.
1) Get the value from the textbox in .NET
eg. string strToDate = txtToDate.text;
2) Add it to the my SqlParameter array by converting it to a datetime
eg.
prmKeyRequest[4] = new SqlParameter();
prmKeyRequest[4].ParameterName = "@ToDate";
prmKeyRequest[4].Direction = ParameterDirection.Input;
prmKeyRequest[4].SqlDbType = SqlDbType.DateTime;
prmKeyRequest[4].Size = 8;
prmKeyRequest[4].Value = Convert.ToDateTime(strToDate).;
/* strToDate is from above */

3) In my stored procedure, I'm accepting this input as a DateTime
@ToDate datetime

4) and attempting to use it in my sql as follows (I'm building the sql
dynamically which is why you see the tic-marks here. I'm only including the
errant portion of my stored proc):
DECLARE @DynamicSql nvarchar(500)
@DynamicSql = 'Select *
From table t
where t.MyDateField < ' + @ToDate
EXEC
{
@DynamicSql
}
Go

RESULT: Does not work. I get the error referenced in the subject line

What type is ToDate of?
It should be DateTime.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

I'm sorry, I pasted the incorrect declaration. I originally declared the
sql
server variables as follows:
@FromDate datetime,
@ToDate datetime

I've been trying all kinds of combinations to try to get this to work.
With
this in mind, do you have any advice?

Hi,

Why do you define both date parameters as nvarchar?
@FromDate nvarchar,
@ToDate nvarchar
You are passing DateTime type from .net and sql server awaits a string
type...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.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

 

[Guest]

Yes, the date is entered as a string on the application side (eg. 12/1/2004).
Before I pass that string to the stored procedure, I convert it to a datetime:
eg. Convert.ToDateTime("12/1/2004");
- Then I'm trying to accept the input in the stored proc as a datetime.
Since that didn't work, I tried your suggestion of passing the datetime in as
a string instead of a datetime and accepting it as an nvarchar(25) in the
stored proc. It doesn't bring back the correct results.

 

[Guest]

I'd like to pass in my input as a datetime variable (from the .NET side) and
accept it as a datetime variable (in the Stored Procedure). How can I
accomplish this and do a comparison with this input on a datetime field?
Right now it is erroring (see subject line).

What type is ToDate of?
It should be DateTime.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

I'm sorry, I pasted the incorrect declaration. I originally declared the
sql
server variables as follows:
@FromDate datetime,
@ToDate datetime

I've been trying all kinds of combinations to try to get this to work.
With
this in mind, do you have any advice?

Hi,

Why do you define both date parameters as nvarchar?
@FromDate nvarchar,
@ToDate nvarchar
You are passing DateTime type from .net and sql server awaits a string
type...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.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

 

[Guest]

Not sure why, but when I try this approach (nvarchar's instead of datetime's)
It doesn't evaluate correctly. It's returning all rows instead of only the
ones that should match my condition. I'd rather pass in my input as a
datetime variable and accept it as a datetime variable. How can I accomplish
this and do a comparison with my input on a datetime field? Right now it is
erroring (see subject line).

 

[Guest]

Thanks for your help. I've figured it out. I got your nvarchar method to
work. I appreciate your time

 

[Ritesh Jain via DotNetMonster.com]

Hi,
Did u figure out what was the exact problem ?? is the Length Parameter of the nvarchar.........?