Hi
i am triying to add seconds inside the stored procedure i am not sure how can i acheive this task
basically i want to add 20 seconds to SVI.VideoStartTime currently its breaking date in hh:m:ss:ms
replicate('0',2-len(cast(datepart(hour,max(SVI.VideoStartTime)) as varchar(max)))) + cast(datepart(hour,max(SVI.VideoStartTime)) as varchar(max)) + ':'
+ replicate('0' ,2 - len(cast(datepart(mi,max(SVI.VideoStartTime)) as varchar(max)))) + cast(datepart(mi,max(SVI.VideoStartTime)) as varchar(max))
+ ':' + replicate('0',2- len(cast(datepart(ss,max(SVI.VideoStartTime)) as varchar(max)))) + cast(datepart(ss,max(SVI.VideoStartTime)) as varchar(max))
+ '.' + replicate('0',3- len(cast(datepart(ms,DATEADD(ms,(SVI.ItemOrder - 1) * 0,max(SVI.VideoStartTime))) as varchar(max)))) + cast(datepart(ms,DATEADD(ms,(SVI.ItemOrder - 1) * 0,max(SVI.VideoStartTime) )) as varchar(max))"in",
the above is a portion of code which deals wid the videostarttime column.
the below is the full procedure details
ALTER PROC [dbo].[pXMLtwo](
@ScheduleId int, -- the scheduleid of the broadcasting day
@UserId Int -- the userid that created this schedule
)
as
set nocount on
declare @xml nvarchar(max)
declare @X1 nvarchar(max)
DECLARE @Seconds INT
SET @Seconds = 20
BEGIN TRY
Set @xml = ''
set @xml = @Xml + (select convert(varchar(max),ScheduleStartDate,103) "date",
replicate('0',2-len(cast(datepart(hour,ScheduleStartDate) as varchar(max)))) + cast(datepart(hour,ScheduleStartDate) as varchar(max)) + ':'
+ replicate('0' ,2 - len(cast(datepart(mi,ScheduleStartDate) as varchar(max)))) + cast(datepart(mi,ScheduleStartDate) as varchar(max))
+ ':' + replicate('0',2- len(cast(datepart(ss,ScheduleStartDate) as varchar(max)))) + cast(datepart(ss,ScheduleStartDate) as varchar(max))
+ '.' + replicate('0',3- len(cast(datepart(ms,ScheduleStartDate) as varchar(max)))) + cast(datepart(ms,ScheduleStartDate) as varchar(max)) "start",
(select UserName from users where UserId = @UserId ) "user"
from dbo.Schedules s
where S.ScheduleID = @ScheduleId
for xml path (''))
set @xml = @Xml + '' + ( select
case when max(SVI.VideoItemID) in( -1,0) then '00000000'
else replicate('0',8-len(cast(max(SVI.VideoItemID) as varchar(max)))) + cast(max(SVI.VideoItemID) as varchar(max))
end "id",
replicate('0',2-len(cast(datepart(hour,max(SVI.VideoStartTime)) as varchar(max)))) + cast(datepart(hour,max(SVI.VideoStartTime)) as varchar(max)) + ':'
+ replicate('0' ,2 - len(cast(datepart(mi,max(SVI.VideoStartTime)) as varchar(max)))) + cast(datepart(mi,max(SVI.VideoStartTime)) as varchar(max))
+ ':' + replicate('0',2- len(cast(datepart(ss,max(SVI.VideoStartTime)) as varchar(max)))) + cast(datepart(ss,max(SVI.VideoStartTime)) as varchar(max))
+ '.' + replicate('0',3- len(cast(datepart(ms,DATEADD(ms,(SVI.ItemOrder - 1) * 0,max(SVI.VideoStartTime))) as varchar(max)))) + cast(datepart(ms,DATEADD(ms,(SVI.ItemOrder - 1) * 0,max(SVI.VideoStartTime) )) as varchar(max))"in",
replicate('0',2-len(cast(datepart(hour,max(SVI.VideoEndTime)) as varchar(max)))) + cast(datepart(hour,max(SVI.VideoEndTime)) as varchar(max)) + ':'
+ replicate('0' ,2 - len(cast(datepart(mi,max(SVI.VideoEndTime)) as varchar(max)))) + cast(datepart(mi,max(SVI.VideoEndTime)) as varchar(max))
+ ':' + replicate('0',2- len(cast(datepart(ss,max(SVI.VideoEndTime)) as varchar(max)))) + cast(datepart(ss,max(SVI.VideoEndTime)) as varchar(max))
+ '.' + replicate('0',3- len(cast(datepart(ms,DATEADD(ms,(SVI.ItemOrder - 1) * 0,max(SVI.VideoEndTime))) as varchar(max)))) + cast(datepart(ms,DATEADD(ms,(SVI.ItemOrder - 1) * 0,max(SVI.VideoEndTime))) as varchar(max)) "out",
CASE WHEN max(SVI.VideoItemID) = -1 then 'break'
WHEN max(SVI.VideoItemID) = 0 then 'adbreak'
ELSE max(VIT.VideoItemTypeName) END "media",
CASE WHEN max(SVI.VideoItemID) = -1 then 'break'
WHEN max(SVI.VideoItemID) = 0 then 'adbreak'
ELSE max(GB.GameName) END "GameName" ,
CASE WHEN max(SVI.VideoItemID) = -1 then 'break'
WHEN max(SVI.VideoItemID) = 0 then 'adbreak'
ELSE max(GB.Publisher) END "GamePublisher" ,
CASE WHEN max(SVI.VideoItemID) = -1 then 'break'
WHEN max(SVI.VideoItemID) = 0 then 'adbreak'
ELSE max(GB.Platform) END "GamePlatform" ,
+ ':' + CASE WHEN max(SVI.VideoItemID) = -1 then 'break'
WHEN max(SVI.VideoItemID) = 0 then 'adbreak'
ELSE max(VI.VideoFileName) END "files"
from dbo.SlotVideoItems SVI
JOIN dbo.Schedules S
ON SVI.ScheduleID = S.ScheduleID
left JOIN dbo.VideoItems VI
ON SVI.VideoItemID = VI.VideoItemID
left JOIN dbo.Game GB
ON VI.GameID = GB.GameId
left JOIN dbo.VideoItemType VIT
ON VI.VideoItemType = VIT.VideoItemType
where S.ScheduleID = @ScheduleId and VIT.VideoItemType =1
group by SVI.ScheduleID,SVI.ScheduledSlotId,SVI.ItemOrder
order by SVI.ScheduleID,SVI.ScheduledSlotId,SVI.ItemOrder
for xml path ('item')) + '' + ''
--select @xml
Select cast(@xml as xml) XmlFile
-- declare @Msg nvarchar(max)
-- set @msg = 'This Procedure Is Under Construction '
-- raiserror(@msg,16,1)
END TRY
BEGIN CATCH
IF @@trancount > 0 rollback tran
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorState = 0 set @ErrorState = 1
if @ErrorNumber <50000 --system
begin --system error
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR (
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
end
else begin --user error
-- Use RAISERROR inside the CATCH block to return error
RAISERROR ( @ErrorMessage, -- Message number.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
end
END CATCH;
