1
DECLARE @Date datetime2(7) = GETDATE()
        ,@Time time(7) = '02:02:00.0000007'
SELECT   @Date [DateTime2(7)], @Time [Time(7)], DATEADD(MS,DATEPART(MS,@Time),@Date) [new_DateTime2(7)]

Output

DateTime2(7)                Time(7)             new_DateTime2(7)
2014-01-20 17:13:02.1970000 02:02:00.0000007    2014-01-20 17:13:02.1970000

Desired Output

DateTime2(7)                Time(7)             new_DateTime2(7)
2014-01-20 17:13:02.1970000 02:02:00.0000007    2014-01-20 19:15:02.1970007

How can I add MS from time(7) to datetime2(7)? The above query doesn't seem to work as expected.

Thank you

007
  • 2,136
  • 4
  • 26
  • 46

1 Answers1

1

What you want to add is NanoSecond not a MicroSecond so you need to change in your code from MS to NS to get desired result.

DECLARE @Date datetime2(7) = GETDATE()
    ,@Time time(7) = '02:02:00.0000007'
SELECT   @Date [DateTime2(7)], @Time [Time(7)], DATEADD(ns,DATEPART(ns,@Time),@Date) [new_DateTime2(7)]


SELECT   DATEPART(MS,@Time) --Result: 0
SELECT   DATEPART(NS,@Time) --Result: 700
  • Doh! (I feel like I need to go back to elementary school after reading this...) hehe..Thank you! Now time to figure out how to add Time(7) to DateTime2(7). – 007 Jan 20 '14 at 18:09
  • 1
    It is always best to `SELECT` what ever you are trying to do before you actually use it in operation, helps to verify what you think is going on with what is actually going on. I think this question will help you with last part http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server –  Jan 20 '14 at 18:15
  • Thanks, that post has many responses that seems related to what I am trying to do. Cheers! – 007 Jan 20 '14 at 18:30