Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL Procedure error - Error converting data type nvarchar to datetime

Posted on 2011-03-08
5
Medium Priority
?
1,112 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 1600 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 400 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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

719 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