title: SQL stored procedure thowing error when converting parameter, no error with literal
body: <p>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:</p>
  
  <p>Table 1:
  Id(Identifier int, not null)
  Message (nvarchar(max)
  DisableComments(int)
  DateTime(datetime)
  Color(nvarchar)
  Username(nvarchar)</p>
  
  <pre><code>ID | Message | DisableComments | DateTime                | Color   | Username 
  18 | Comment |       0         | 2011-12-18 14:16:27.000 | #000000 | User
  </code></pre>
  
  <p>Here is query that works fine:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is query that throws error: "Conversion failed when converting date and/or time from character string.":</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And here is my stored procedure that I am executing:</p>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <p><strong>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.</strong></p>
  
  <pre><code>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
  </code></pre>