Click here to Skip to main content
13,006,319 members (58,204 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 
 from PItable p
 inner join
 PITrantbl pt
 p.ObjectID = pt.PID
 where p.KPIid=@KPI and pt.Month=MONTH(@monthyear)
Posted 3-Apr-13 0:24am
Updated 3-Apr-13 0:25am
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 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
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.
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
Web01 | 2.8.170628.1 | Last Updated 3 Apr 2013
Copyright © CodeProject, 1999-2017
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