Results 1 to 10 of 10

Thread: Subtract date and time fields in SQL Server query

  1. #1

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Subtract date and time fields in SQL Server query

    I have four fields in table1 in Sql Server 2012.

    Two are of type date (storing date1 and date2) and two are of type datetime (storing time1 and time2).
    How do I calculate the difference in hours between date1 + time1 and date2 + time2 in a query?

    Thanks
    PK

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Subtract date and time fields in SQL Server query

    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Subtract date and time fields in SQL Server query

    As a general tip, there's no need to separate those fields. A DateTime is sufficient to hold both a Date and a Time. If you'd done that it would be a simple matter of using the DateDiff function Sapator has linked to.

    As you've got the time in it's own field things will get a little more sketchy as we don't really know what's in the Date part of the DateTime field - if it's holding actual Dates then how does that interact with the Date Fields if they differ?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: Subtract date and time fields in SQL Server query

    Code:
    declare @my_date datetime
    	, @date_only date
    	, @time_only datetime
    
    set @my_date = getdate()
    
    SELECT @my_date AS [Original date & time]
    
    --      get date only,       get time only
    SELECT @date_only = @my_date, @time_only = cast(@my_date as float) - floor(cast(@my_date as float))
    
    -- display the values
    SELECT @date_only as [Date only], @time_only as [Time only]
    
    
    -- convert date to datetime, then to float
    -- you cannot convert date to float, but datetime CAN be converted to float
    -- add the 2 float numbers, then convert back to datetime
    select cast(cast(cast(@date_only as datetime) as float) + cast(@time_only as float) as datetime) AS [date & time combined]
    Once you combine the date & time into one, then you can just use DATEDIFF(hour, start_date, end_date)

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Subtract date and time fields in SQL Server query

    The worry is that this:-
    Code:
    @time_only = cast(@my_date as float) - floor(cast(@my_date as float))
    ...assumes that the content of the Date part of the DateTime field holds no significance.

    If that's true then your suggestion is basically what I'd suggest (I'd suggest casting it to a Time because it's more explicit but it wouldn't surprise me if the float operation you're is actually more performant). If the date part of the DateTime field is significant then we'd need to understand what it contains.

    Basically, since the introduction of the Date and Time types there's really no need to ever use part of a DateTime field. You either use a DateTime or a Date and a Time, depending on you predicted access pattern.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Subtract date and time fields in SQL Server query

    Lets not forget DATETIMEOFFSET... I have to use this since we are across time zones. We always store UTC datetime and then use AT TIMEZONE 'XXXX' to display the local time
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Subtract date and time fields in SQL Server query

    I wasn't aware of the DateTimeOffset so thanks for bringing it up. I tried to Rep you but couldn't so you'll have to settle for a hearty "Thanks".
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Subtract date and time fields in SQL Server query

    Funky the thanks is appreciated. Rep is OK but a thanks is better
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9

    Thread Starter
    Fanatic Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    784

    Re: Subtract date and time fields in SQL Server query

    GaryMazzone and FunkyDexter,

    Thanks for your valuable contributions.
    I have tried that of FunkyDexter and it works excellent.

    PK

  10. #10
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    Re: Subtract date and time fields in SQL Server query

    Hello, @Peekay

    To Subtract date and time fields in SQL Server then follow below query.

    Code:
    DECLARE @StartTime DATETIME = '2011-09-23 15:00:00',
                  @EndTime   DATETIME = '2011-09-23 17:54:02'
           
    SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,@StartTime, @EndTime),0), 108) as ElapsedTime
    I hope above query will be useful for you.
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width