How to use datediff() for columns?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

I wanna know how to differentiate a time period in a day between two columns like in time and out time?

Posted 24-May-15 20:55pm

Member 11698441

Add a Solution

Comments

Thanks7872 25-May-15 3:23am

What have you tried?

2 solutions

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)

RossMW · Accepted Answer · 2015-05-24T21:09:00

Solution 1

Refer to http://www.w3schools.com/sql/func_datediff.asp[^][datediff^]

For time just use the appropriate abbreviation. Start and end date can be the columns

Posted 24-May-15 21:09pm

RossMW

Comments

Member 11698441 25-May-15 3:15am

no while hardcoding the date its given but how to give fr column in table wer to giv FROM<tablename>

OriginalGriff · Accepted Answer · 2015-05-24T21:15:00

Solution 2

Try:

SQL

SELECT DATEDIFF(hh, startDateColumn, endDateColumn) As [Span In Hours] FROM MyTable

Posted 24-May-15 21:15pm

OriginalGriff

Comments

RossMW 25-May-15 3:18am

Your quick. Bet me to it ha ha

Member 11698441 25-May-15 3:29am

the problem is like per day there may be multiple entrys and out i wanna group it and add everything per day

OriginalGriff 25-May-15 3:45am

So what is the problem? SUM the GROUP difference...

Member 11698441 25-May-15 4:01am

there are multiple dates and i have to select for individual date

OriginalGriff 25-May-15 4:11am

You are going to have to explain the whole problem, with the tables and some sample data and the output you expect, rather than you feeding us drips and drabs of information and hoping we luckily fall into the solution you want! :laugh:

Remember that we can't see your screen, access your HDD, or read your mind...

Member 11698441 25-May-15 4:19am

ok fine i'll say to u in a detailed manner... there is a list of datas in table with employeeinstance,date, in time and outime i wanna find the total working hours of each employee and also should find the break hours he took and also should check the condition whether he has worked for 9 hrs or not. if he worked less hw much hours he is deficit by this is my situation

OriginalGriff 25-May-15 4:49am

"...with the tables and some sample data and the output you expect, rather than you feeding us drips and drabs of information..."

Make sense?

Member 11698441 25-May-15 6:58am

ill say the colums fr u id,card_id, time_out,time_in,transaction_date, update_date,update_user, employeeinstance

Member 11698441 25-May-15 6:59am

i want output like total time of working hours for a individual employee instance and it should be above 9 hrs if less wanna know how much he is deficit this to be done fr each day

Member 11698441 25-May-15 6:54am

502 NULL 2013-12-01 08:00:00.000 2013-12-01 13:00:00.000 2013-12-01 00:00:00.000 NULL NULL 244
514 NULL 2013-12-16 08:00:00.000 2013-12-16 18:00:00.000 2013-12-16 00:00:00.000 NULL NULL 326
516 NULL 2013-12-05 09:00:00.000 2013-12-05 19:00:00.000 2013-12-05 00:00:00.000 NULL NULL 256
517 NULL 2013-12-06 07:00:00.000 2013-12-06 17:00:00.000 2013-12-06 00:00:00.000 NULL NULL 256
520 NULL 2013-12-09 09:00:00.000 2013-12-09 19:00:00.000 2013-12-09 00:00:00.000 NULL NULL 326
521 NULL 2013-12-10 08:30:00.000 2013-12-10 18:00:00.000 2013-12-10 00:00:00.000 NULL NULL 326
525 NULL 2013-12-17 07:50:00.000 2013-12-17 18:07:00.000 2013-12-17 00:00:00.000 NULL NULL 244
526 NULL 2013-12-18 08:18:00.000 2013-12-18 19:07:00.000 2013-12-18 00:00:00.000 NULL NULL 244
527 NULL 2013-12-19 09:00:00.000 2013-12-19 20:00:00.000 2013-12-19 00:00:00.000 NULL NULL 244
529 NULL 2013-12-04 08:00:00.000 2013-12-04 18:00:00.000 2013-12-04 00:00:00.000 NULL NULL 262
530 NULL 2013-12-05 08:30:00.000 2013-12-05 17:30:00.000 2013-12-05 00:00:00.000 NULL NULL 262
532 NULL 2013-11-05 08:00:00.000 2013-11-05 18:00:00.000 2013-11-05 00:00:00.000 NULL NULL 262