CODEWITHSUNDEEP
sqlsql-insert
Peki Peki
Peki Peki

Reputation: 7

SQL Insert type time statement

I have issue and no idea how to solve my Insert Stored Procedure, I have problem with data type Time to insert inside of it,

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

ALTER PROCEDURE [dbo].[TimbraturaDataImport3]

@BadgeTimbrature varchar(50)

AS

DECLARE 
@Terminale nvarchar(12),
@IdBadge int,
@Year int,
@Month int,
@Day int,
@Time Time(5),
@IO int;

SET @Terminale = SUBSTRING(@BadgeTimbrature, 1, 12);
SET @IdBadge = SUBSTRING(@BadgeTimbrature, 13, 18);
SET @Year = SUBSTRING(@BadgeTimbrature, 19, 20);
SET @Month = SUBSTRING(@BadgeTimbrature, 21, 22);
SET @Day = SUBSTRING(@BadgeTimbrature, 23, 24);
SET @Time = SUBSTRING(@BadgeTimbrature, 25, 28);
SET @IO = SUBSTRING(@BadgeTimbrature, 29, 30);

BEGIN TRANSACTION

INSERT INTO [dbo].[BadgeTimbrature]
           ([Terminale]
           ,[IdBadge]
           ,[Year]
           ,[Month]
           ,[Day]
           ,[Time]
           ,[IO])
     VALUES
           (@Terminale
           ,@IdBadge
           ,@Year
           ,@Month
           ,@Day
           ,@Time
           ,@IO)

COMMIT TRANSACTION

And in Table BadgeTimbrature I have Column [Time] - time(7) and result of it: "19:32:00.0000000"

So my SubString Int is for example 1932, how to insert it in time format (19:32:00.0000000)?

Upvotes: 0

Views: 30

Answers (1)

EzLo
EzLo

Reputation: 14209

Assuming you are using SQL Server (from your code), you can correctly cast an INT value with format HHMM to TIME by casting to VARCHAR first and stuffing a : in between.

DECLARE @Int INT = 2205

SELECT CONVERT(TIME, STUFF(CONVERT(VARCHAR(10), @Int), 3, 0, ':'))

On this case the STUFF is placing at the position 3, while replacing 0 characters, the string :.

If you already have the value as VARCHAR, just omit the conversion:

SET @Time = CONVERT(TIME(5), STUFF(SUBSTRING(@BadgeTimbrature, 25, 28), 3, 0, ':'))

Upvotes: 1

Related Questions