calculating date & time difference from 4 text fields

  • I am using MS SQL Server 2000.

    I have 4 text fields

    1. event begin date

    2. event begin time

    3. event end date

    4. event end time

    I have to find the day and time difference in months days hours and  minutes between the event begin date & time and the event end date & time – The event begin date and event end date are char(8) & event begin time and event end time are char(4) – with military time. I would need to combine the event begin date & event begin time to get the event begin date  & time and the same for the event end date & time.

    If the values of the 4 text fields are

    event begin date = ‘20070207’, event begin time = ‘2015’, event end date = ‘20070208’, event end time = ‘0105’, the difference should be 0 month 0 day 4 hours 50 minutes.

    Can somebody please help with MS SQL Server 2000 syntax?

  • This is a bit long-winded and there may be a better way (you could also make this into a function) but:

    DECLARE @beginDate VARCHAR(8)

    DECLARE @beginTime VARCHAR(8)

    DECLARE @finalDate VARCHAR(8)

    DECLARE @finalTime VARCHAR(8)

    SET @beginDate = '20070207'

    SET @beginTime = '0000'

    SET @finalDate = '20070208'

    SET @finalTime = '0705'

    DECLARE @beginDateTime DATETIME

    DECLARE @finalDateTime DATETIME

    DECLARE @minDiff INT

    DECLARE @result VARCHAR(100)

    SET @beginTime = LEFT(@beginTime,2) + ':' + SUBSTRING(@beginTime,3,2) + ':' + '00'

    SET @finalTime =  LEFT(@finalTime,2) + ':' + SUBSTRING(@finalTime,3,2) + ':' + '00'

    SET @beginDateTime = CONVERT(datetime, @beginDate, 112) + CONVERT(datetime, @beginTime, 108)

    SET @finalDateTime = CONVERT(datetime, @finalDate, 112) + CONVERT(datetime, @finalTime, 108)

    SELECT @minDiff = DATEDIFF(mi, @beginDateTime, @finalDateTime)

    SET @result = ''

    IF @minDiff > (60*24)

    BEGIN

     SET @result = @result + CAST((@minDiff / 60/ 24) AS VARCHAR) + ' Days, '

     SET @minDiff = @minDiff - (ROUND((@minDiff /60/24), 0, 1) * 60 * 24)

    END

    ELSE

     SET @result = @result + '0 Days, '

    IF @minDiff > 60

    BEGIN

     SET @result = @result + CAST((@minDiff / 60) AS VARCHAR) + ' Hours, '

     SET @minDiff = @minDiff - (ROUND((@minDiff / 60), 0, 1) * 60 )

    END

    ELSE

     SET @result = @result + '0 Hours, '

    IF @minDiff > 0

    BEGIN

     SET @result = @result + CAST(ISNULL(@minDiff,0) AS VARCHAR) + ' Minutes'

     SET @minDiff = @minDiff - ROUND(@minDiff, 0, 1)

    END

    SELECT @result



    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

  • If possible, change the dates and times to use datetime.

    Also, how are you going to define a month? For days, hours and minutes the following should work:

    SELECT D.MiDiff/1440 AS Days

            ,D.MiDiff%1440/60 AS Hours

            ,D.MiDiff%1440%60 AS Minutes

    FROM ( SELECT DATEDIFF(mi, BeginDate + ' ' + LEFT(BeginTime, 2)

                                    + ':' + RIGHT(BeginTime, 2)

                            ,EndDate + ' ' + LEFT(EndTime, 2)

                                    + ':' + RIGHT(EndTime, 2))

            FROM YourTable) D (MiDiff)

  • Here's Ken's version, which works quite well, using Stuff() instead of Left() and Right():

    SELECT D.MiDiff/1440 AS Days

    ,D.MiDiff%1440/60 AS Hours

    ,D.MiDiff%1440%60 AS Minutes

    FROM ( SELECT DATEDIFF(mi, BeginDate + ' ' + Stuff(BeginTime,3,0,':')

    , EndDate + ' ' + Stuff(EndTime,3,0,':'))

    FROM YourTable) D (MiDiff)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply