Solved

SQL Time Difference and Comparison

Posted on 2014-02-05
1
589 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now