March 8, 2012 at 3:14 pm
I have a create_timestamp and modify_timestamp that are datetime data types. I need to find the difference between the two. Then I need the result set to show only those values where the difference between modify_timestamp and create_timestamp is GREATER than 1 second...
I have an interface that is inserting records and populating the modify timestamp FIRST than the create timestamp. This is resulting in 50 - 60% of the modify_timestamps are GREATER than the create time by milliseconds. I need to take those values OUT of the equation and only show those where the modify time is greater than 1 second.
March 8, 2012 at 3:20 pm
Use DATEDIFF()???
Example:DECLARE @Time1 datetime = GETDATE()
WAITFOR DELAY '00:00:05:789'
DECLARE @Time2 datetime = GETDATE()
SELECT DATEDIFF(ss, @Time1, @Time2)
result: 5s
-- So Something like...
WHERE modify_timestamps > DATEDIFF(ss, modify_timestamp, create_timestamp) > 1
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 8, 2012 at 3:28 pm
Thank you for the reply. Unfortunately the query doesn't like " > 1". It throughs Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '>'.
March 8, 2012 at 3:43 pm
This slight modification works for me
IF DATEDIFF(ss,@create_timestamp,@modify_timestamp)> 1
BEGIN
PRINT 'GOT IT'
END
ELSE
PRINT 'WHOOPS'
March 8, 2012 at 3:45 pm
Will this work?
WHERE modify_timestamp > DATEADD(ss, 1, create_timestamp)
March 8, 2012 at 3:47 pm
LOL - sorry I didn't meant to use that directly, it was just a concept to show you how to calculate the difference between the two datetime fields. Of course there's a syntax error 😀 What I wrote there won't actually work...in fact I didn't realize I had wrote what I did 🙂
It would help if you posted some sample data and your actual SELECT statement...you can use temp table to play with the data and
Try this:
WHERE DATEDIFF(ss, modify_timestamp, create_timestamp) > 1
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 9, 2012 at 9:24 pm
Tank you to everyone. The work the eeryone posted helped me figure out what I needed to do. Thanks again and I appreciate your postings. 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply