How can i fix conversion failed when converting date and/or time from character string error

Question

SQL CODE

alter procedure [dbo].[urunGiris]
@TableName varchar(100),
@MalzemeStokNo varchar(50),
@MalzemeAd varchar(100),
@Irsaliye varchar(100),
@MalzemeAgirlik varchar(50),
@GirenMiktar int,
@GirenTonaj FLOAT (53),
@CikanMiktar int,
@CikanTonaj FLOAT (53),
@KalanMiktar int,
@KalanTonaj FLOAT (53),
@Tarih datetime 
As
Begin
    declare @SQL nvarchar(max)
	set @SQL = 'insert into ' + @TableName + '(MalzemeStokNo, MalzemeAd, Irsaliye, MalzemeAgirlik,GirenMiktar,GirenTonaj,CikanMiktar,CikanTonaj,KalanMiktar,KalanTonaj,Tarih) 
	values (' + @MalzemeStokNo + ', ' + QUOTENAME(@MalzemeAd) + ', ' + @Irsaliye + ', ' + @MalzemeAgirlik + ',' + CAST(@GirenMiktar AS nvarchar(50)) + ',
	' + CAST(@GirenTonaj AS nvarchar(50)) + ',' + CAST(@CikanMiktar AS nvarchar(50)) + ',' + CAST(@CikanTonaj AS nvarchar(50)) + ',
	' + CAST(@KalanMiktar AS nvarchar(50)) + ',' + CAST(@KalanTonaj AS nvarchar(50)) + ',' + TRY_CAST(@tarih as datetime)  + ')
		'
 Execute sp_executesql @sql, N'@TabName varchar(100),@MalzemeStokNo1 varchar(50),@MalzemeAd1 varchar(100),@Irsaliye1 varchar(100),@MalzemeAgirlik1 varchar(50),
 @GirenMiktar1 int,@GirenTonaj1 FLOAT (53),@CikanMiktar1 int,@CikanTonaj1 FLOAT (53),@KalanMiktar1 int,@KalanTonaj1 FLOAT (53),@Tarih1 datetime', 
 @TabName = @TableName,
 @MalzemeStokNo1=@MalzemeStokNo,
 @MalzemeAd1=@MalzemeAd,
 @Irsaliye1=@Irsaliye,
 @MalzemeAgirlik1=@MalzemeAgirlik,
 @GirenMiktar1=@GirenMiktar,
 @GirenTonaj1 =@GirenTonaj,
 @CikanMiktar1 =@CikanMiktar,
 @CikanTonaj1 =@CikanTonaj,
 @KalanMiktar1 =@KalanMiktar,
 @KalanTonaj1 =@KalanTonaj,
 @Tarih1 = @Tarih
End

C# CODE

cmd.Parameters.Add("Tarih",SqlDbType.DateTime).Value = DateTime.Now;

Answer 1

Try the following:

CREATE OR ALTER PROCEDURE [dbo].[urunGiris]
	@TableName VARCHAR(100)
	, @MalzemeStokNo VARCHAR(50)
	, @MalzemeAd VARCHAR(100)
	, @Irsaliye VARCHAR(100)
	, @MalzemeAgirlik VARCHAR(50)
	, @GirenMiktar INT
	, @GirenTonaj FLOAT(53)
	, @CikanMiktar INT
	, @CikanTonaj FLOAT(53)
	, @KalanMiktar INT
	, @KalanTonaj FLOAT(53)
	, @Tarih DATETIME
AS
	BEGIN
		DECLARE @SQL NVARCHAR(MAX);
		SET @SQL = N'insert into ' + @TableName
				   + N'(MalzemeStokNo, MalzemeAd, Irsaliye, MalzemeAgirlik,GirenMiktar,GirenTonaj,CikanMiktar,
CikanTonaj,KalanMiktar,KalanTonaj,Tarih) 
	values (@MalzemeStokNo1,
@MalzemeAd1,
@Irsaliye1,
@MalzemeAgirlik1,
@GirenMiktar1,
@GirenTonaj1,
@CikanMiktar1,
@CikanTonaj1,
@KalanMiktar1,
@KalanTonaj1,
@Tarih1)';
		EXECUTE sp_executesql
			@SQL
			, N'@MalzemeStokNo1 varchar(50),@MalzemeAd1 varchar(100),@Irsaliye1 varchar(100),@MalzemeAgirlik1 varchar(50),
 @GirenMiktar1 int,@GirenTonaj1 FLOAT (53),@CikanMiktar1 int,@CikanTonaj1 FLOAT (53),@KalanMiktar1 int,@KalanTonaj1 FLOAT (53),@Tarih1 datetime'
			, @MalzemeStokNo1 = @MalzemeStokNo
			, @MalzemeAd1 = @MalzemeAd
			, @Irsaliye1 = @Irsaliye
			, @MalzemeAgirlik1 = @MalzemeAgirlik
			, @GirenMiktar1 = @GirenMiktar
			, @GirenTonaj1 = @GirenTonaj
			, @CikanMiktar1 = @CikanMiktar
			, @CikanTonaj1 = @CikanTonaj
			, @KalanMiktar1 = @KalanMiktar
			, @KalanTonaj1 = @KalanTonaj
			, @Tarih1 = @Tarih;
	END;

Answer 2

The dynamic SQL does not add the appropriate single quotes around the VARCHAR and DATETIME parameters. You can see this for yourself using the PRINT command to write the value of @SQL to the messages window in SQL management studio. I recommend using GETDATE() in TSQL rather than passing the DateTime.Now from C#.

ALTER procedure [dbo].[urunGiris]
@TableName varchar(100),
@MalzemeStokNo varchar(50),
@MalzemeAd varchar(100),
@Irsaliye varchar(100),
@MalzemeAgirlik varchar(50),
@GirenMiktar int,
@GirenTonaj FLOAT (53),
@CikanMiktar int,
@CikanTonaj FLOAT (53),
@KalanMiktar int,
@KalanTonaj FLOAT (53),
@Tarih datetime 
As
Begin
    declare @SQL nvarchar(max)
	set @SQL = 'insert into ' + @TableName + '(MalzemeStokNo, MalzemeAd, Irsaliye, MalzemeAgirlik,GirenMiktar,GirenTonaj,CikanMiktar,CikanTonaj,KalanMiktar,KalanTonaj,Tarih) 
	values (' + @MalzemeStokNo + ', ' + QUOTENAME(@MalzemeAd) + ', ' + @Irsaliye + ', ' + @MalzemeAgirlik + ',' + CAST(@GirenMiktar AS nvarchar(50)) + ',
	' + CAST(@GirenTonaj AS nvarchar(50)) + ',' + CAST(@CikanMiktar AS nvarchar(50)) + ',' + CAST(@CikanTonaj AS nvarchar(50)) + ',
	' + CAST(@KalanMiktar AS nvarchar(50)) + ',' + CAST(@KalanTonaj AS nvarchar(50)) + ',' + CONVERT(VARCHAR(19), GETDATE(), 20)   + ')
		'
PRINT @SQL
RETURN

 Execute sp_executesql @sql, N'@TabName varchar(100),@MalzemeStokNo1 varchar(50),@MalzemeAd1 varchar(100),@Irsaliye1 varchar(100),@MalzemeAgirlik1 varchar(50),
 @GirenMiktar1 int,@GirenTonaj1 FLOAT (53),@CikanMiktar1 int,@CikanTonaj1 FLOAT (53),@KalanMiktar1 int,@KalanTonaj1 FLOAT (53),@Tarih1 datetime', 
 @TabName = @TableName,
 @MalzemeStokNo1=@MalzemeStokNo,
 @MalzemeAd1=@MalzemeAd,
 @Irsaliye1=@Irsaliye,
 @MalzemeAgirlik1=@MalzemeAgirlik,
 @GirenMiktar1=@GirenMiktar,
 @GirenTonaj1 =@GirenTonaj,
 @CikanMiktar1 =@CikanMiktar,
 @CikanTonaj1 =@CikanTonaj,
 @KalanMiktar1 =@KalanMiktar,
 @KalanTonaj1 =@KalanTonaj,
 @Tarih1 = @Tarih
End

insert into TableName(MalzemeStokNo, MalzemeAd, Irsaliye, MalzemeAgirlik,GirenMiktar,GirenTonaj,CikanMiktar,CikanTonaj,KalanMiktar,KalanTonaj,Tarih) 
	values (MalzemeStokNo, [MalzemeAd], Irsaliye, MalzemeAgirlik,1,
	1.2,2,2.2,
	3,3.3,2023-02-16 07:01:07)

Lastly, the sp_executesql syntax does not make a lot of sense since there are no SQL parameters in the dynamic SQL string.

Go back and debug your code.

Answer 3

' + TRY_CAST(@tarih as datetime) + ')

It's the same error and the same cause as in your other post, only difference, here it's a date value you can "add" to string, you also have to convert it to varchar.

https://learn.microsoft.com/en-us/answers/questions/1180575/sql-stored-procedure-inserting-convert-error

Answer 4

Hi @ÖMER NASUHİ KESKİN

Although you made improvements to your previous code, you still haven't noticed the problem.

declare @SQL nvarchar(max)

In your code, you set the @sql to nvarchar, so you need to make sure that the string to the right of '=' also needs to be nvarchar. Like Olaf mentioned datetime, and 'insert into' and the @Irsaliye you set to type varchar above, etc.

Dynamic SQL is a complex feature, and you need to use it for complex work, so you must be careful when writing code to avoid problems as much as possible. Otherwise, if you report an error at runtime, it will be difficult to find the problem in the whole string of code.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.