Solved

SQL Time Difference and Comparison

Posted on 2014-02-05
1
600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 25

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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