question

txbiernacki avatar image
txbiernacki asked

error Conversion failed when converting date and/or time from character string.

I have been stuck on this issue and can't figure out how to fix it. I have a stored procedure that is supposed show current tmp tables. I needed to add a create_date column into the table and as soon as I did that is where I get the error. Can you help? ..... SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[CRC_dbaTableInfo] as set nocount on; -- exec [dbo].[CRC_dbaTableInfo] DECLARE @TblNames Table ( COUNTER INT IDENTITY(1,1), tbl_name nvarchar(200) NULL, create_date datetime ) DECLARE @TableSizes AS TABLE ([TblName] VARCHAR(255), [NumRows] INT, [Reserved_Size] VARCHAR(20), [Data_Size] VARCHAR(20), [Index_Size] VARCHAR(20), [Used] VARCHAR(20), create_date datetime) DECLARE @ROWCOUNT INT DECLARE @I INT DECLARE @str nvarchar(100) DEclare @A datetime SET @I = 1 INSERT INTO @TblNames(tbl_name, create_date) SELECT t.create_date, s.NAME +'.'+t.name FROM hmxafo.sys.Tables t JOIN HMXafo.sys.Schemas s ON s.SCHEMA_ID = t.schema_id SET @ROWCOUNT = @@ROWCOUNT WHILE @I <= @ROWCOUNT BEGIN set @str = (select tbl_name FROM @TblNames WHERE COUNTER = @I) set @A =(select create_date from @TblNames where COUNTER =@I) INSERT INTO @TableSizes EXEC sp_spaceused @str, @A SET @I = @I +1 END SELECT *, cast(left(Data_Size, charindex(' ',Data_Size)) as decimal) / 1024 as [Data_Size MB] FROM @TableSizes where TblName like 'tmp%' or TblName like 'temp%' order by Data_Size desc -- exec dbo.crc_dbatableinfo
datetimecast-convert
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please do not post duplicate questions
0 Likes 0 ·

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
Is it the line: INSERT INTO @TableSizes EXEC sp_spaceused @str, @A that's causing problems? What I would do is change `@TableSizes` so it doesn't have the `created_date`, and get that information from @TableNames. If you must have the `created_date` field in `@TableSizes`, then create it as a NULLable field, change your `INSERT INTO...` statement to specifically insert into the columns that sp_spaceused returns, and then populate the `created_date` field afterwards.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.