Solved

MSSQL Procedure error - Error converting data type nvarchar to datetime

Posted on 2011-03-08
5
1,105 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
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

929 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now