?
Solved

Converting to Hrs and mins

Posted on 2011-03-22
3
Medium Priority
?
207 Views
Last Modified: 2012-05-11
How can I convert seconds to Hrs and minutes using sql command?

pls help.

ayha
0
Comment
Question by:ayha1999
3 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 35190782
Do you want this to happen on sqlserver or in code ?
declare
	@sec	int

select	@sec = 64597

select	right('0' + rtrim(convert(char(2), @sec / (60 * 60))), 2) + ':' + 
	right('0' + rtrim(convert(char(2), (@sec / 60) % 60)), 2) + ':' + 
	right('0' + rtrim(convert(char(2), @sec % 60)),2)

Open in new window

0
 
LVL 8

Expert Comment

by:Anurag Agarwal
ID: 35190785
try this !!

DECLARE @SecondsToConvert int
SET @SecondsToConvert = 10000  (your seconds)


-- Declare variables
DECLARE @Hours int
DECLARE @Minutes int
DECLARE @Seconds int
DECLARE @Time datetime

-- Set the calculations for hour, minute and second
SET @Hours = @SecondsToConvert/3600
SET @Minutes = (@SecondsToConvert % 3600) / 60
SET @Seconds = @SecondsToConvert % 60

-- Store the datetime information retrieved in the @Time variable
SET @Time = (SELECT
RTRIM(CONVERT(char(8), @Hours) ) + ':' +
CONVERT(char(2), @Minutes) + ':' +
CONVERT(char(2), @Seconds));

-- Display the @Time variable in the format of HH:MM:SS
SELECT CONVERT(varchar(8),CONVERT(datetime,@Time),108)

0
 
LVL 7

Author Closing Comment

by:ayha1999
ID: 35190951
thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

864 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