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

Why does the SP take a DateTime as an NVARCHAR? Why not just use the DATETIME datatype? — MatBailie
It's the same thing, if you look at SP taking it as datetime would result only in having to convert to nvarchar on different places. I tried taking it as DATETIME and then converting to nvarchar where needed but I was getting similar error but I couldn't figure where. — formatc
No, sorry, it's expressly not the same thing. DateTime's represented as string have formats like dd-mm-yyyy or mm-dd-yyyy. Get the format wrong in the conversions and you get errors. These formats may be assumed, implicit, server settings, or language sensitive, and are a serious headache to track down. You should only convert datetime to a string for representation, not in order to pass values around. I would not be surprised if changing to DATETIME type fixes your problem. (Also, from a quick read through, you actually convert fewer times if you use the native type) — MatBailie
+1 @Dems - data types exist for very good reasons. Using strings for everything is a very serious anti-pattern. — JNK
Also, consider sp_executesql in place of your exec. This will allow you to use parameters, and so pass the DATETIME values as DATETIME type, rather than having to convert to strings. Moving datetime values back and forth between string and native types is a real headache, and it seems to have you in it's claws. If you could post the version with a DATETIME native type and the errors there, we can look at that (including sp_executesql) and get you to a much cleaner (and hopefully working) answer. — MatBailie

msmucker0527 msmucker0527 · Accepted Answer · 2012-01-13T14:32:13

The procedure is fine, the execution is not working

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

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

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

1 Answers