Solved

MSSQL Procedure error - Error converting data type nvarchar to datetime

Posted on 2011-03-08
5
1,111 Views
Last Modified: 2013-12-17
Hi,
I have a MSSQL Procedure with parameters:

udsp_RNumberOfLegacyDeals (@dtDateFrom datetime, @dtDateTo datetime, @strPropCode varchar(18))
SET DATEFORMAT DMY
set  @dtDateTo = @dtDateTo + ' 23:59:00'
SELECT 
COUNT(DISTINCT CASE WHEN T_OP.DealType = 4 AND 
PropFor in (5) AND (PropForUse in (19, 20))
THEN T_Property.PropCode ELSE NULL END) AS LegacyAllHouses
FROM T_Property INNER JOIN 
(SELECT DISTINCT PropCode, DealType, RegDate, PrevDate
FROM T_OwnerProp
WHERE (RegDate BETWEEN CONVERT(datetime,@dtDateFrom,104) AND CONVERT(datetime,@dtDateTo,104) 
	OR PrevDate BETWEEN CONVERT(datetime,@dtDateFrom,104) AND CONVERT(datetime,@dtDateTo,104))
) T_OP ON T_Property.PropCode = T_OP.PropCode

WHERE T_Property.PropCode LIKE @strPropCode AND (T_Property.PropStatus <> 10 OR T_Property.PropStatus IS NULL)

Open in new window


If I call this procedure from Management Studio, it works OK, but if I call this procedure from C# (ASP.NET) it gives error:
Error converting data type nvarchar to datetime.

Error is in parameter - @dtDateTo. It is in format 'DD.MM.YYYY'. If I pass format in 'MM.DD.YYYY' it works.

Why it works in Management Studio, but does not work in ASP.NET code ?

As I understand, SET DATEFORMAT DMY will not work without GO after it.
0
Comment
Question by:exceter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 400 total points
ID: 35078843
try changing the datatype of @dtDateTo to varchar(10) and call
0
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35078848
which date format you are using while calling this procedure from management studio
0
 
LVL 3

Author Comment

by:exceter
ID: 35079268
which date format you are using while calling this procedure from management studio
I use SET DATEFORMAT DMY
0
 
LVL 3

Author Comment

by:exceter
ID: 35079274
try changing the datatype of @dtDateTo to varchar(10) and call
It surely works, but what I can't figure out, why it works in Management Studio and does not work in ASP.NET code ?
0
 
LVL 5

Assisted Solution

by:mayankagarwal
mayankagarwal earned 100 total points
ID: 35079275
as far as i know this is the problem with the date formats of .net and mssql.
You can just write a function which will change the format of date and after changing the format call the procedure
0

Featured Post

 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question