Click here to Skip to main content
12,244,056 members (52,098 online)
Rate this:
 
Please Sign up or sign in to vote.
I tried Google and trying to solve this error but of no use. the error still persists.

When tried to execute this line

cmd.CommandText = string.Format("[dbo].[search_PIValue] {0},'{1}'", kpi,monthyear);

I got this error:

Error converting data type nvarchar to date.

I don't know what to change and where to make a change since i'm a newbie programmer.
Please help me.

This is my stored procedure:
ALTER proc [dbo].[search_PIValue]
(
 
@KPI int,
@monthyear DATE 
)
as
begin
  select 
 p.ObjectID,p.PIName,pt.PIvalue
 
 from PItable p
 inner join
 PITrantbl pt
 on
 p.ObjectID = pt.PID
 
 where p.KPIid=@KPI and pt.Month=MONTH(@monthyear)
end
Posted 3-Apr-13 1:24am
Velrats1.3K
Edited 3-Apr-13 1:25am
v2
Comments
patel_vijay 3-Apr-13 6:28am
   
What is the value of "monthyear" parameter that you are passing?
Velrats 3-Apr-13 6:38am
   
{14/05/2012 12:00:00 AM}

This is the value thats being passed @Patel_vijay
patel_vijay 3-Apr-13 6:44am
   
Try to format your "monthyear" value in yyyy-MM-dd HH.mm.ss format.
It will work

Ex. MONTH('2012-05-14 12:00:00)
Velrats 3-Apr-13 7:06am
   
I'm confused Where to make this change!?

in c# code or in SQL query?

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Assuming that monthyear is a DateTime (and it looks like it is from your example) pass it through as a Parameter instead of converting it at all:
cmd.CommandText = string.Format("[dbo].[search_PIValue] {0},@MY", kpi);
cmd.Parameters.AddWithValue("@MY", monthyear);
SQL should then sort it all out itself.
  Permalink  
Comments
Velrats 3-Apr-13 8:53am
   
Thanks a lot @OriginalGriff it solved my problems and runs fine now :)
OriginalGriff 3-Apr-13 9:25am
   
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 3 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100