none
Calculating time difference in T-SQL

    Question

  • Hi there!

    I hope someone can help me with this - I'm new to programming in T-SQL and I'm getting frustrated with this:

    We have a database where the date is stored as datetime with the time being midnight for all records.  The time is stored in a varchar field called time.  How do I calculate the difference between time 1 and time 2, given that the two events may be on different days as well as at different times?

    Thank you.
    Thursday, January 14, 2010 3:17 PM

Answers

  • Naom,

    When concatenating strings the dataype with the higer precedence is going make the other data type convert.  Since the datetime data type has higher precedence the varchar is converted to a datetime, which looks like below.

    SELECT CONVERT(DATETIME,[Time]) AS TimeConverted
    FROM @T
    /*
    -----------------------
    1900-01-01 01:00:00.000
    1900-01-01 14:15:00.000
    */

    After the time column becomes a datetime the optimizer uses simple additon to get the final result.  Which is essentially like using the dateadd function to add Dat and time to the time of 1900-0101 01:00:00.000.

    I forgot to add that the space in between the dat and time columns is not need the higher precedence is going to convert the value anyway.

    http://jahaines.blogspot.com/
    Thursday, January 14, 2010 4:56 PM

All replies

  • Try
    declare @d datetime = dateadd(day,0,datediff(day,0, getdate()))
    declare @time varchar(20) = '05:05:05 PM'
    
    select cast(CONVERT(varchar(10),@d,110) + SPACE(1) + @time as datetime)
    
    -- so
    
    select cast(CONVERT(varchar(10),DateField,110) + SPACE(1) + TimeField as datetime) from myTable
    

    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 14, 2010 3:23 PM
  • Is this how the data being saved? How do you want the end results?

    DECLARE @t TABLE(Dat DATETIME,[Time] VARCHAR(20))
    INSERT INTO @t
    SELECT '01-12-2010 00:00:000', '01:00:00 AM' UNION ALL
    SELECT '01-13-2010 00:00:000', '02:15:00 PM'
    
    SELECT DAT + ' ' + [Time]
    FROM @T

    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, January 14, 2010 3:28 PM
  • Interesting - looks like it's doing some smart implicit conversions.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 14, 2010 3:30 PM
  • Naom,

    When concatenating strings the dataype with the higer precedence is going make the other data type convert.  Since the datetime data type has higher precedence the varchar is converted to a datetime, which looks like below.

    SELECT CONVERT(DATETIME,[Time]) AS TimeConverted
    FROM @T
    /*
    -----------------------
    1900-01-01 01:00:00.000
    1900-01-01 14:15:00.000
    */

    After the time column becomes a datetime the optimizer uses simple additon to get the final result.  Which is essentially like using the dateadd function to add Dat and time to the time of 1900-0101 01:00:00.000.

    I forgot to add that the space in between the dat and time columns is not need the higher precedence is going to convert the value anyway.

    http://jahaines.blogspot.com/
    Thursday, January 14, 2010 4:56 PM
  • Thank you Adam, I think I'm on the right track now!
    Thursday, January 14, 2010 6:29 PM
  • Thanks - very helpful to know.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, January 14, 2010 6:31 PM