-- ====================================================================================================================================
-- Author:      Monica Lupu
-- Create date: 06/06/2008
-- Description:    Compare the patch timestamp for an 1.04 upgrade to a SIT timestamp
-- Input:       sit_timestamp datetime – timestamp from sit
--            patch_timestamp nvarchar(30) – timestamp for 1.04 update (format sample: '2008-06-04T13:58:31+0000')
-- Returns: 0 – sit_timestamp is older than patch_timestamp
--          1 - equal
--        2 – otherwise
-- ====================================================================================================================================
CREATE FUNCTION compare_timestamp
(
    @sit_timestamp nvarchar(30),
    @patch_timestamp nvarchar(30)
)
RETURNS int
AS
BEGIN
    declare @sit_datetime datetime, @patch_datetime datetime                    
    set @sit_datetime = CONVERT(datetime, REPLACE(LEFT(@sit_timestamp, CHARINDEX ('+' , @sit_timestamp)-1), 'T', ' '),20)
    set @patch_datetime = CONVERT(datetime, REPLACE(LEFT(@patch_timestamp, CHARINDEX ('+' , @patch_timestamp)-1), 'T', ' '),20)

    if @sit_datetime < @patch_datetime
        RETURN 0
    else if @sit_datetime > @patch_datetime
        RETURN 2

    declare @sit_msec int, @patch_msec int
    set @sit_msec = RIGHT(@sit_timestamp, LEN(@sit_timestamp)-CHARINDEX ('+' , @sit_timestamp))
    set @patch_msec = RIGHT(@patch_timestamp, LEN(@patch_timestamp)-CHARINDEX ('+' , @patch_timestamp))
    
    if @sit_msec  < @patch_msec
        RETURN 0
    else if @sit_msec > @patch_msec
        RETURN 2
    RETURN 1
END

GO