CODEWITHSUNDEEP
sqlsql-server-2012
Ankur Rana
Ankur Rana

Reputation: 88

Msg 8114 : Error converting data type timestamp to datetime in SQL Server 2012

Someone in our team wrote the following query years ago 

    create table #tempDocumentCategory (documentcategorycodeidno int , documentcategorycode varchar(50), documentcategorycodedescription varchar(200), timestamp datetime,Inactive bit)
    insert #tempDocumentCategory(documentcategorycodeidno  , documentcategorycode , documentcategorycodedescription , timestamp ,Inactive )
    exec sp_getDocumentCategoriesCodesCtrl 1

Now this exec sp_getDocumentCategoriesCodesCtrl returns a timestamp which is then stored in the datetime type column in the temporary table created above. We never recieved any problem until recently a customer raised an issue stating that the page is crashing with an error message stating "Error Converting data type timestamp to datetime".

Now, I understand that a timestamp is not a datetime and is not being used here as such. As far as I can check I see that the datetime and Timestamp both have same size - bytes(8). I want to know why timestamp didn't convert to datetime automatically and What am I missing/misinterpreting here. Pardon if this is a stupid question, I am fairly new to SQL.

CREATE PROCEDURE sp_getDocumentCategoriesCodesCtrl
@ShowAllInd bit = 0
AS
/* Select record set */
IF @ShowAllInd = 0
   SELECT
      DocumentCategoryCodeIdNo,
      DocumentCategoryCode,
      DocumentCategoryCodeDescription,
      Timestamp,
      CAST(0 as BIT) as Inactive
   FROM tDocumentCategory_Codes
   WHERE 
      (DocumentCategoryCodeToEffectDate = '01/01/3000')
   ORDER BY DocumentCategoryCode
ELSE
   SELECT
      DocumentCategoryCodeIdNo,
      DocumentCategoryCode,
      DocumentCategoryCodeDescription,
      Timestamp,
      Inactive =
         CASE WHEN DocumentCategoryCodeToEffectDate <> '01/01/3000' THEN CAST(1 as BIT)
         ELSE CAST(0 as BIT)
         END
   FROM tDocumentCategory_Codes
   ORDER BY DocumentCategoryCode
RETURN(0)

Table Definition

CREATE TABLE [dbo].[tDocumentCategory_Codes](
    [DocumentCategoryCodeIdNo] [int] IDENTITY(1,1) NOT NULL,
    [DocumentCategoryCodeDomainIdNo] [int] NOT NULL,
    [DocumentCategoryCode] [nvarchar](50) NOT NULL,
    [DocumentCategoryCodeDescription] [nvarchar](200) NULL,
    [DocumentCategoryCodeFromEffectDate] [datetime] NOT NULL,
    [DocumentCategoryCodeToEffectDate] [datetime] NOT NULL,
    [LanguageId] [char](5) NULL,
    [DocumentCategoryCodeDefaultCodeInd] [bit] NULL,
    [Timestamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_tDocumentCategory_Codes] PRIMARY KEY CLUSTERED 
(
    [DocumentCategoryCodeIdNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Upvotes: 1

Views: 1612

Answers (1)

Juozas
Juozas

Reputation: 935

You are storing data in [timestamp] column with data type timestamp. Data type timestamp does not have any relations with datetime data type. Timestamp is just automaticaly incremented value on database. So, wrong decision is to convert timestamp to datetime. You can solve your problem in following way:

   create table #tempDocumentCategory(
          documentcategorycodeidno int 
         ,documentcategorycode varchar(50)
         ,documentcategorycodedescription varchar(200)
         ,timestamp VARBINARY(8)
         ,Inactive bit
    )

If you need to have datetime, you need to alter your table [tDocumentCategory_Codes], change [timestamp] column type to DATETIME, and then set default value (GETDATE()). At this case you can use DATETIME format in #tempDocumentCategory and get your desired result

Upvotes: 1

Related Questions