Extracting time in hours and mins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Extracting time in hours and mins

Author

Topic

Jack121
Starting Member

14 Posts

Posted - 2013-06-08 : 04:21:41

Hello..I am having trouble with this calculation
Users enter data into 2 fields 1. Dischargedate 2.Dischargetime
I need to write a query that will calculate the hours and mins from todays date. Bearing in mind the discharge date can only be equal to or greater than todays date.
I tried this but I get some wierd time especially if dischargedate and getdate() are the same

SELECT CONVERT(Varchar, (DATEPART(dd, Dischargedate - GETDATE()* 24 + DATEPART(hh,Dischargedate - GETDATE() + 'h:' + CONVERT(Varchar, DATEPART(mi, Dischargedate - GETDATE()) + 'm' AS ElapsedTime
FROM Main

not sure where I am going wrong, can some tell me the right way to calculate this ..thanks very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 04:28:32

what are the datatypes of DischargeDate and DischargeTime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Jack121
Starting Member

14 Posts

Posted - 2013-06-08 : 04:35:22

both are datetime datatypes..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 04:47:56

then this would do assuming you'll only pass date part for DischargeDate


SELECT CAST(DATEDIFF(dd,0,TimeDiff) * 24 + DATEPART(hh,TimeDiff) AS varchar(5)) + ':' + DATENAME(minute,TimeDiff)
FROM
(
SELECT DATEADD(minute,DATEDIFF(minute,GETDATE(),DischargeDate + ' ' + DischargeTime),0) AS TimeDiff
FROM table
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Jack121
Starting Member

14 Posts

Posted - 2013-06-08 : 05:06:09

That works...can you tell me how to add the letter h and m in the results as well...thanks very much so it looks like e.g 30h:40m

thanks again!..

Jack121
Starting Member

14 Posts

Posted - 2013-06-08 : 05:34:41

Hi again..this is going into a gridview..is there a way to do this with only one select statement being used?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-08 : 13:50:14

[code]
SELECT CAST(DATEDIFF(dd,0,DATEADD(minute,DATEDIFF(minute,GETDATE(),DischargeDate + ' ' + DischargeTime),0)) *24 + DATEPART(hh,DATEADD(minute,DATEDIFF(minute,GETDATE(),DischargeDate + ' ' + DischargeTime),0)) AS varchar(5)) + 'h:' + DATENAME((minute,DATEADD(minute,DATEDIFF(minute,GETDATE(),DischargeDate + ' ' + DischargeTime),0)) + 'm'
FROM table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Jack121
Starting Member

14 Posts

Posted - 2013-06-09 : 07:55:55

ok thanks very much for your help, I ran that SQL but got this error
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

I decided to break it down into smaller parts to find where the problem is

this bit is working

SELECT CAST(DATEDIFF(dd,0,DATEADD(minute,DATEDIFF(minute,GETDATE(),
DischargeDate + ' ' + DischargeTime),0)) *24 + DATEPART(hh,DATEADD(minute,DATEDIFF(minute,GETDATE(),DischargeDate + ' ' + DischargeTime),0)) AS varchar(5)) + 'h:'
from table

this bit throws the error

select DATENAME((minute,DATEADD(minute,DATEDIFF(minute,GETDATE(),
DischargeDate + ' ' + DischargeTime),0)) + 'm'
from table

not sure what is causing the syntax error though..

thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-09 : 11:51:36

it should be


select DATENAME(minute,DATEADD(minute,DATEDIFF(minute,GETDATE(),
DischargeDate + ' ' + DischargeTime),0)) + 'm'
from table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Jack121
Starting Member

14 Posts

Posted - 2013-06-09 : 13:11:31

thanks so much!..it works!!!!
Still have to figure out how exactly it works though,I would ask you to explain it in plain English, but I think that would be a bit much. Off to figure it out!!!..thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources