[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL subtract military time to get minutes

Posted on 2011-05-10
6
Medium Priority
?
1,019 Views
Last Modified: 2012-06-21
Hello experts,

I am trying to subtract two variables and come up with a duration in minutes:

declare @end_time char(4)
declare @begin_time char(4)
declare @duration int

set @end_time = 1400
set @begin_time = 1350

set @duration = DateDiff(mi, DATEADD(mi,@end_time,0), DATEADD(mi,@begin_time,0))

select @duration

Thoughts?

Thanks
0
Comment
Question by:robthomas09
6 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 1200 total points
ID: 35731602
Are the times being fed in as 1400 instead of 14:00 or can this be changed?  If so then the following should work.  
declare @end_time smalldatetime
declare @begin_time smalldatetime
declare @duration int

set @end_time = '14:00'
set @begin_time = '13:50'

set @duration = DateDiff(mi, @end_time, @begin_time)

select @duration

Open in new window

0
 
LVL 18

Assisted Solution

by:dj_alik
dj_alik earned 100 total points
ID: 35731613
@end_time  and  @begin_time  must be datetime
 and not char(4)
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 700 total points
ID: 35731854
try this:

set @end_time = 1400
set @begin_time = 1350
set @diff int
set @diff = 60 * (@end_time - @begin_time) / 100

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 61

Expert Comment

by:HainKurt
ID: 35731882
what is 1350? is it 13:50 or 13.5? meaning 13:30?
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 700 total points
ID: 35731916
this may work


declare @end_time int = 1450
declare @begin_time int = 1330
declare @duration int

set @duration =((@end_time / 100) - (@begin_time/100)) * 60 + (@end_time % 100) -(@begin_time % 100)

select @duration 

--> 80

Open in new window

0
 

Author Closing Comment

by:robthomas09
ID: 35732306
Thanks!
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.​
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 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