I Need hour min and sec in a queryAsk Questions

 Posted on 08/22/2018

I am calculating the time difference between 2 times, I want to print the hour min and sec. Can anyone please tell me how to do it.

My query

SELECT 
    CONVERT(VARCHAR(8), DATEADD(ms, DATEDIFF(ms, CONVERT(VARCHAR(8), GETDATE(), 114), CONVERT(VARCHAR(8), VCTime, 114)), 0), 114) AS TImeDifference 
FROM
    Test

Output:

TimeDifference  
---------------
10:51:37    
20:51:37   
21:51:37   
22:21:37   
08:51:37   
00:51:37   

Expected Output

TimeDifference  
---------------
10h:51m:37s    
20h:51m:37s   
21h:51m:37s   
22h:21m:37s   
08h:51m:37s   
00h:51m:37s 

I am calculating the time difference between 2 times, I want to print the hour min and sec. Can anyone please tell me how to do it.

My query

SELECT 
    CONVERT(VARCHAR(8), DATEADD(ms, DATEDIFF(ms, CONVERT(VARCHAR(8), GETDATE(), 114), CONVERT(VARCHAR(8), VCTime, 114)), 0), 114) AS TImeDifference 
FROM
    Test

Output:

TimeDifference  
---------------
10:51:37    
20:51:37   
21:51:37   
22:21:37   
08:51:37   
00:51:37   

Expected Output

TimeDifference  
---------------
10h:51m:37s    
20h:51m:37s   
21h:51m:37s   
22h:21m:37s   
08h:51m:37s   
00h:51m:37s   

I am calculating the time difference between 2 times, I want to print the hour min and sec. Can anyone please tell me how to do it.

My query

SELECT 
    CONVERT(VARCHAR(8), DATEADD(ms, DATEDIFF(ms, CONVERT(VARCHAR(8), GETDATE(), 114), CONVERT(VARCHAR(8), VCTime, 114)), 0), 114) AS TImeDifference 
FROM
    Test

Output:

TimeDifference  
---------------
10:51:37    
20:51:37   
21:51:37   
22:21:37   
08:51:37   
00:51:37   

Expected Output

TimeDifference  
---------------
10h:51m:37s    
20h:51m:37s   
21h:51m:37s   
22h:21m:37s   
08h:51m:37s   
00h:51m:37s   

I am calculating the time difference between 2 times, I want to print the hour min and sec. Can anyone please tell me how to do it.

My query

SELECT 
    CONVERT(VARCHAR(8), DATEADD(ms, DATEDIFF(ms, CONVERT(VARCHAR(8), GETDATE(), 114), CONVERT(VARCHAR(8), VCTime, 114)), 0), 114) AS TImeDifference 
FROM
    Test

Output:

TimeDifference  
---------------
10:51:37    
20:51:37   
21:51:37   
22:21:37   
08:51:37   
00:51:37   

Expected Output

TimeDifference  
---------------
10h:51m:37s    
20h:51m:37s   
21h:51m:37s   
22h:21m:37s   
08h:51m:37s   
00h:51m:37s   

I am calculating the time difference between 2 times, I want to print the hour min and sec. Can anyone please tell me how to do it.

My query

SELECT 
    CONVERT(VARCHAR(8), DATEADD(ms, DATEDIFF(ms, CONVERT(VARCHAR(8), GETDATE(), 114), CONVERT(VARCHAR(8), VCTime, 114)), 0), 114) AS TImeDifference 
FROM
    Test

Output:

TimeDifference  
---------------
10:51:37    
20:51:37   
21:51:37   
22:21:37   
08:51:37   
00:51:37   

Expected Output

TimeDifference  
---------------
10h:51m:37s    
20h:51m:37s   
21h:51m:37s   
22h:21m:37s   
08h:51m:37s   
00h:51m:37s   

I am calculating the time difference between 2 times, I want to print the hour min and sec. Can anyone please tell me how to do it.

My query

SELECT 
    CONVERT(VARCHAR(8), DATEADD(ms, DATEDIFF(ms, CONVERT(VARCHAR(8), GETDATE(), 114), CONVERT(VARCHAR(8), VCTime, 114)), 0), 114) AS TImeDifference 
FROM
    Test

Output:

TimeDifference  
---------------
10:51:37    
20:51:37   
21:51:37   
22:21:37   
08:51:37   
00:51:37   

Expected Output

TimeDifference  
---------------
10h:51m:37s    
20h:51m:37s   
21h:51m:37s   
22h:21m:37s   
08h:51m:37s   
00h:51m:37s   
Share On: facebook gplus twitter
profile
Asked by on 08/22/2018 Score: 89 points
Add Comment:

Comments

1 Answers

0 Corrected Answers
Aproved Answers
1
Profile
Answered by Haresh Chaudhari on 9/15/2018 6:57:09 PM Score: 343 points

Hi Kunal

Directly there is no any formula or inbuilt function in SQL where you can format time like that, but you may use few tricks to make your sql query and get your desire output.

I take current time by using GetDate() in SQL to help you with similar example. Look this query.

DECLARE @CurrentDate AS DATETIME
SET
@CurrentDate=GETDATE()

Select SUBSTRING(CONVERT(VARCHAR(8), @CurrentDate,114),0,3)+'h:'+SUBSTRING(CONVERT(VARCHAR(8),@CurrentDate,114),4,2)+'m:'+
SUBSTRING
(CONVERT(VARCHAR(8),@CurrentDate,114),7,3)+'s'

I hope this help you to figure out as per your requirement. Don't hesitate if you need any help.
Thanks

Comments

Add Comment:

Post Your Answers

Existing Members

Sign in to your account
Email Address
Password
New Member?
Sign up and complete profile
Full Name
Email Address
I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the StoodQ newsletters
Guideline to answer a question:

Useful tips to submit your answer
Please read below guidelines before you submit your answer for question.

  • Read and understand question for which you are submitting your answer.
  • Try to avoid grammatical and spell mistake while answering.
  • Do not post any irrelevant information in your answer.
  • Explain your answer with example or any reference link to help who posted question.
  • If you find irrelevant question, please report it to support. Click here to contact support.
  • You agree to the privacy policy and terms of use to submit any contents.

Note: StoodQ is online developers community which helps developer for their difficulty, lets help them with your value contribution.