Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1114
  • Last Modified:

MSSQL Procedure error - Error converting data type nvarchar to datetime

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
exceter
Asked:
exceter
  • 2
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
try changing the datatype of @dtDateTo to varchar(10) and call
0
 
mayankagarwalCommented:
which date format you are using while calling this procedure from management studio
0
 
exceterAuthor Commented:
which date format you are using while calling this procedure from management studio
I use SET DATEFORMAT DMY
0
 
exceterAuthor Commented:
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
 
mayankagarwalCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now