x

add seconds to complex xml based stored procedure

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;

more ▼

asked Apr 13, 2010 at 10:59 AM in Default

Matti Butt gravatar image

Matti Butt
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x415
x150

asked: Apr 13, 2010 at 10:59 AM

Seen: 997 times

Last Updated: Apr 13, 2010 at 10:59 AM