Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: Change to "Total Hours" Difference

    How can I change the output into the total hours between 2 smalldatetime data? TIF

    Code:
    DECLARE @iDAY AS SmallDatetime
    SET @iDAY = '2004-08-12 10:05:00'
    
    
    PRINT @iDAY
    PRINT GETDATE()
    
    PRINT GETDATE()-@iDAY
    +++++++++++++++
    Aug 12 2004 10:05AM
    Aug 13 2004 10:05AM
    Jan  2 1900 12:00AM   <--- I want to get the total hours (i.e. 24)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT DateDiff(minute, '2004-08-12 10:05', '2004-08-13 10:05') / 60
    -PatP

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    Thanks. That did the trick.

    I found out that I can also use this one instead.

    Code:
    PRINT DATEDIFF(hour,GETDATE(),@iDAY)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think that using hour will give you what you want, although it might. Using hour will return the number of hour boundaries crossed, which isn't what I think you want. Consider the case of:
    Code:
    SELECT DateDiff(minute, '2004-08-12 10:55', '2004-08-13 10:05') / 60
    , DateDiff(hour, '2004-08-12 10:55', '2004-08-13 10:05')
    -PatP

  5. #5
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104
    It looks OK. It's rounding off giving me a minor difference but I'm not really particular with exact figures.

    Thanks again.

    Code:
    DECLARE @iDAY AS SmallDatetime
    SET @iDAY = '2004-08-12 11:34:00'
    
    PRINT @iDAY
    PRINT GETDATE()
    
    PRINT DATEDIFF(minute,GETDATE(),@iDAY)/60
    PRINT DATEDIFF(hour,GETDATE(),@iDAY)
    
    **** RESULTS ****
    
    Aug 12 2004 11:34AM
    Aug 17 2004 10:32AM
     
    -118
    -119

Posting Permissions

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