Query for Time Differences in Hours

Question

Post reply

Query for Time Differences in Hours

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

In that case, what would be wrong with this ?

/*

What would be wrong with This solution ?

Keep in mind it starts with date 1900-01-01

*/

;With cteSum

as (

Select sum ( datediff(ss, StartTime , EndTime ) * (1 + datediff(dd,StartDate, EndDate)) )as Elapsed_ss

from @SSC

)

Select dateadd(ss, Elapsed_ss, 0) as UserUsed_datetime

from cteSum

edited: I see andrewd.smith already proposed a likewise solution whilst I was posting my reply.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Meera SSCommitted Points: 1966 More actions · Answer 2

Dear All, Thks for ur kind sharing of ur ideas for my accomplishments.

But very sorry if in case i am bothering u ppl in further.

All of u had taken the value of the inputs given directly, in order to insert those into a temp table.

But to me i need to insert values referring to another table fields.

when i refer such way its not giving out the correct value. can u help me in further Plzz...

the modified query wat i get in final(which gives out correct answer),

select datediff(mi,CONVERT(datetime, '9:30',108),CONVERT(datetime, '14:00',108))/60.00

* (datediff(day, CONVERT(datetime,'15/04/09',3), CONVERT(datetime,'16/04/09',3)) + 1)

which i need to replace the value of time & date by table field name like the below,

select datediff(mi,CONVERT(datetime, starttime,108),CONVERT(datetime, endtime,108))/60.00

* (datediff(day, CONVERT(datetime,startdate,3), CONVERT(datetime,enddate,3)) + 1)

but while doing so, the time 'starttime' is treated as datetime field which is taking in as a date not as time.

Again for ur kind info my table is like this,

Id---|Startdate---|EndDate---|Starttime--|endtime--|

1----|15/04/09---|16/04/09---|9:30------|14:00----|

The time field can hold only characters hh:mm alone.

can any one help me with any appropriate convertion or correct declaration of time as hh:mm query? so that i hope can get a query which gets me solution..

Thanks a lot for all of you !!!

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

We use @temp tables to provide a usable example.

Just replace the table name in the query and use the currect column names and you should be fine.