How to find differences of two time in sql server

Question

0.00/5 (No votes)

See more:

I have a windows form that gives me time_in and time_out for all the users. Time_in and time_out are also tables in my sql database. I'd like to find work hours which can be calculated as time_out - time_in. Time_in and time_out are time format (hh:mm:ss). How to find the work hour ? The code that I tried doesn't work becuase of the data variable type. But I couldn't do this with the int data type anyway.

string time_in = databaseTimeIn;
string time_out = databaseTimeOut;
string workhour = time_out-time_in;

Posted 8-Dec-14 7:28am

fatihkaratay

Add a Solution

Comments

Kornfeld Eliyahu Peter 8-Dec-14 13:31pm

If you want to do calculations with time, than use type that stores the values AS time and not as string!!!

3 solutions

Solution 1

Don't use strings for date and time data! If you are using SQL Server, DATETIME values can be operated on directly, or use DATEDIFF.

http://msdn.microsoft.com/en-us/library/ms189794.aspx[^]

Posted 8-Dec-14 7:37am

PIEBALDconsult

Comments

/\jmot 9-Dec-14 0:28am

+5,

Solution 2

use

DATEDIFF(datepart,startdate,enddate)
eg: SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

ans:61

Posted 8-Dec-14 17:18pm

Kan07

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mudgilsks · Accepted Answer · 2014-12-08T17:46:00

Solution 3

hi,

to calculate Work Hours you need to convert in_time and out_time in HH format so use the formula in_time as HH+mm/60+ss/60/60 i.e 07:23:55-->>07+23/60+55/60/60=7.39 hrs

after that calculate Work Hours as diff of out-in

Posted 8-Dec-14 17:46pm

mudgilsks