# 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

• 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