CODEWITHSUNDEEP
c#mysqlsql-serverstored-procedures
user2583182
user2583182

Reputation: 185

conversion of a nvarchar data type to a datetime data type in stored procedure

I am getting error when I pass date date for start date and end date that is string value from textbox to sql. 

alter procedure [dbo].[sp_insert_ProjectDetail]    
(@projectname varchar(max),    
@projectnos varchar(20),    
@Task_assigned varchar(max),  
@Startdate varchar(30),    
@enddate varchar(30),    
@status varchar(20),  
@analyst varchar(max),    
@qualitycheck varchar(max) 
)as    

declare @value as varchar(20)    

INSERT INTO tbl_ProjectDetails(ProjectName,ProjectNos,Task_assigned,Startdate,enddate,Analyst,Qualitycheck,flag,Status_Name)VALUES (@projectname,@projectnos,@Task_assigned,@Startdate,@enddate,@analyst,@qualitycheck,0,@status)

Upvotes: 1

Views: 442

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062520

I am getting error

Top tip; always post the exact error message, and ideally sample input values that create that error; I strongly suspect that it is no coincidence that you're asking this question on 13th January; you might want to check that yesterday's data didn't record as the 1st of December (and Sunday the 1st of November, etc). However... moving on...

@Startdate varchar(30),    
@enddate varchar(30),

The fundamental trick here is: don't do that. And certainly don't just blindly cast those values by treating them "as is". The recommended approach is to parse the values at the calling layer, so those might become:

@Startdate datetime,
@enddate datetime,

(or similar; there are other date/time data types; it should match the table), and worry about the format in the calling code:

var start = DateTime.Parse[Exact](startText, ...); // some expected format/culture/etc
...
cmd.Parameters.AddWithValue("Startdate", start);

(etc)

Upvotes: 2

Related Questions