Solved

MSSQL Procedure error - Error converting data type nvarchar to datetime

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

830 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