Solved

SQL Time Difference and Comparison

Posted on 2014-02-05
1
592 Views
Last Modified: 2014-02-05
Hello Experts,

I am currently developing a script that will compare times and get the time difference using the following script.

declare @update_time datetime
declare @current_time datetime
--declare @inactive varchar(1)
--variable to hold y/n value if time difference indicates table to be inactive

set @update_time = (
SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'NGDemo')
AND OBJECT_ID=OBJECT_ID('aa_test')
)

set @current_time = GETDATE()

select convert(varchar(5),DateDiff(s, @update_time, @current_time)/3600)+':'+
convert(varchar(5),DateDiff(s, @update_time, @current_time)%3600/60)+
':'+convert(varchar(5),(DateDiff(s, @update_time, @current_time)%60)) as [hh:mm:ss]

Open in new window


Would it be possible to set the @inactive variable to 'Y' if the time difference is greater than say 1 hour?
0
Comment
Question by:robthomas09
1 Comment
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39837509
If I understood you correctly, this is what you are after:
declare @update_time datetime
declare @current_time datetime
declare @inactive varchar(1)
--variable to hold y/n value if time difference indicates table to be inactive

set @update_time = (
SELECT last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'NGDemo')
AND OBJECT_ID=OBJECT_ID('aa_test')
)

set @current_time = GETDATE()

select convert(varchar(5),DateDiff(s, @update_time, @current_time)/3600)+':'+
convert(varchar(5),DateDiff(s, @update_time, @current_time)%3600/60)+
':'+convert(varchar(5),(DateDiff(s, @update_time, @current_time)%60)) as [hh:mm:ss],
@inactive = CASE WHEN DateDiff(s, @update_time, @current_time) >= 3600 THEN 'Y' ELSE 'N' END

Open in new window

0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question