SQL stored procedure thowing error when converting parameter, no error with literal

Question

Hi I have problem that I can't solve alone since damn debugging doesn't work on my host.In short When I try to convert type datetime to varchar from one column in Table 1 and using it as parametar to my stored procedure I get error, but when I write exectly same thing but with N'..string...' everything is fine, Im really confused, here it is:

Table 1: Id(Identifier int, not null) Message (nvarchar(max) DisableComments(int) DateTime(datetime) Color(nvarchar) Username(nvarchar)

ID | Message | DisableComments | DateTime                | Color   | Username 
18 | Comment |       0         | 2011-12-18 14:16:27.000 | #000000 | User

Here is query that works fine:

DECLARE @return_value int

SELECT TOP 1 [ID]
      ,[Message]
      ,[DisableComments]
      ,[DateTime]
      ,[Color]
      ,[Username] 

FROM Thoughts

EXEC    @return_value = InsertThoughtToPartition
        @ThoughtMessage = Message,
        @ThoughtDateTime = N'2012-01-03 01:22:31.000',
        @ThoughtColor = Color,
        @ThoughtUsername = Username

SELECT  'Return Value' = @return_value

Here is query that throws error: "Conversion failed when converting date and/or time from character string.":

DECLARE @return_value int

SELECT TOP 1 [ID]
      ,[Message]
      ,[DisableComments]
      ,[DateTime]
      ,[Color]
      ,[Username]
      ,CONVERT(nvarchar(MAX),DateTime, 121) as Datei   

FROM Thoughts 

EXEC    @return_value = InsertThoughtToPartition
        @ThoughtMessage = Message,
        @ThoughtDateTime = Datei,
        @ThoughtColor = Color,
        @ThoughtUsername = Username

SELECT  'Return Value' = @return_value

And here is my stored procedure that I am executing:

USE [TagCloudDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE InsertThoughtToPartition
(
@ThoughtMessage as nvarchar(MAX),
@ThoughtDateTime as nvarchar(MAX),
@ThoughtColor as nvarchar(MAX),
@ThoughtUsername as nvarchar(MAX)
)
AS

DECLARE @MonthName nvarchar(MAX);
DECLARE @CurrentYear nvarchar(MAX);
DECLARE @InsertTableName nvarchar(MAX);
Declare @CreateTable nvarchar(MAX);
Declare @JustInsert nvarchar(MAX);

SET @CurrentYear =  CAST((SELECT DATENAME(year, CAST(@ThoughtDateTime as datetime))) as nvarchar(MAX)); 
SET @MonthName = CAST((SELECT DATENAME(month, CAST(@ThoughtDateTime  as datetime))) as nvarchar(MAX));
SET @InsertTableName = 'Thoughts_' + @MonthName + '_' + @CurrentYear;

IF OBJECT_ID(@InsertTableName) IS NOT NULL
BEGIN
SET @JustInsert = 'INSERT INTO '+ @InsertTableName + '(Message,DateTime,Color,Username)
     VALUES('''+ @ThoughtMessage+''',CONVERT(DATETIME,'''+ @ThoughtDateTime +''', 121),'''+@ThoughtColor+''','''+@ThoughtUsername+''')';

EXEC(@JustInsert);
END
ELSE
BEGIN

SET @CreateTable = '
USE [TagCloudDb] 
CREATE TABLE ['+ @InsertTableName+'](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [DateTime] [datetime] NOT NULL,
    [Color] [nvarchar](max) NOT NULL,
    [Username] [nvarchar](max) NOT NULL,
    UniqueID as CAST(ID as nvarchar) +''-''+ CONVERT(VARCHAR(8), DateTime, 112) 
) ON [PRIMARY]

INSERT INTO '+ @InsertTableName + '(Message,DateTime,Color,Username)
     VALUES('''+ @ThoughtMessage+''',CONVERT(DATETIME,'''+ @ThoughtDateTime + ''', 121),'''+@ThoughtColor+''','''+@ThoughtUsername+''')';

EXEC(@CreateTable);

END
GO

---

here is updated version with DateTime as input and but still I get same error with two queryes: First query works fine agian but when i try to pass Datei or [DateTime] from first table I get Error converting data type nvarchar to datetime.

USE [TagCloudDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE InsertThoughtToPartition
(
@ThoughtMessage as nvarchar(MAX),
@ThoughtDateTime as DateTime,
@ThoughtColor as nvarchar(MAX),
@ThoughtUsername as nvarchar(MAX)
)
AS

DECLARE @MonthName nvarchar(MAX);
DECLARE @CurrentYear nvarchar(MAX);
DECLARE @InsertTableName nvarchar(MAX);
Declare @CreateTable nvarchar(MAX);
Declare @JustInsert nvarchar(MAX);
DECLARE @JustInsertParamDef nvarchar(MAX);

SET @CurrentYear =  DATENAME(year, @ThoughtDateTime); 
SET @MonthName = DATENAME(month, @ThoughtDateTime);
SET @InsertTableName = 'Thoughts_' + @MonthName + '_' + @CurrentYear;

SET @JustInsert = N'INSERT INTO '+ @InsertTableName + '(Message, DateTime, Color, Username)
     VALUES(@ThoughtMessage, @ThoughtDateTime ,@ThoughtColor, @ThoughtUsername)';

SET @JustInsertParamDef = N'@InsertTableName nvarchar(MAX), @ThoughtMessage nvarchar(MAX),  @ThoughtDateTime datetime,
                            @ThoughtColor nvarchar(MAX), @ThoughtUsername nvarchar(MAX)';

IF OBJECT_ID(@InsertTableName) IS NOT NULL
BEGIN   

EXECUTE sp_executesql
        @JustInsert,
        @JustInsertParamDef,
        @InsertTableName,
        @ThoughtMessage,
        @ThoughtDateTime,
        @ThoughtColor,
        @ThoughtUsername;

END
ELSE
BEGIN

SET @CreateTable = 'USE [TagCloudDb] 
                    CREATE TABLE ['+@InsertTableName+'](
                        [ID] [int] IDENTITY(1,1) NOT NULL,
                        [Message] [nvarchar](max) NOT NULL,
                        [DateTime] [datetime] NOT NULL,
                        [Color] [nvarchar](max) NOT NULL,
                        [Username] [nvarchar](max) NOT NULL,
                        [UniqueID] as CAST(ID as nvarchar) + ''-'' + CONVERT(VARCHAR(8), DateTime, 112) 
                    ) ON [PRIMARY]'


EXEC(@CreateTable);

EXECUTE sp_executesql
        @JustInsert,
        @JustInsertParamDef,
        @InsertTableName = @InsertTableName,
        @ThoughtMessage = @ThoughtMessage,
        @ThoughtDateTime = @ThoughtDateTime,
        @ThoughtColor = @ThoughtColor,
        @ThoughtUsername = @ThoughtUsername;

END

Answer 1

The procedure is fine, the execution is not working

Check the contents of your table after you run the working example.

ID|Message|DateTime|Color|Username|UniqueID

1|Message|2012-01-03 01:22:31.000|Color|Username|1-20120103

You aren't passing the values you selected to the procedure, so it's failing when trying to parse 'Dateti' to a DATETIME type

You should definitely clean up your data types and string sizes, that should make things like this easier to catch