How to add datetime in SQL

Question

0.00/5 (No votes)

See more:

I need to sum time in employee attendance Table.

Attendance Table Fields

ID         numeric(18)-(Auto Increment field)
EmpID      numeric(18)
EntryType  nvarchar(25)
EntryTime  datetime

Table Values Look Like Below

ID    EmpID     EntryType    EntryTime
1     1001       IN          2019-03-31 08:30:00 AM
2     1003       IN          2019-03-31 08:45:00 AM
3     1003       OUT         2019-03-31 05:30:00 AM
4     1004       OUT         2019-03-31 05:35:00 AM

Now i need to sum In and Out time of an given employee How to do it?

What I have tried:

i'm searching goolge the results are
"use DateADD Function"
But I don't know Please Help Me Sir

Posted 5-Apr-19 2:25am

Flower@12

Updated 24-Apr-19 1:32am

Add a Solution

Comments

W∴ Balboos, GHB 22-Apr-19 11:27am

When you finally figure out how to use the data from the answers, consider, as well, how to make sure you actually have pairs of IN/OUT times for each employee - if they didn't do an OUT - what will you do? What if they skipped OUT on day one and IN day two ? Summing the daily differences is, in fact, the easy part.

4 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)

OriginalGriff · Answer 1 · 2019-04-05T02:36:00

You can't add two DateTime values, any more than you can add two telephone numbers - the result is not anything sensible or useful. You can subtract two DateTimes, which give you a time span, which can be expressing in several ways, minutes being one of them.

So start by "matching up" your IN and OUT times for your employees - that's probably going to be hard enough for you at this stage - and then look at subtracting them to find how long they worked for. When you have that, you can start aggregating the data to get your totals.

Maciej Los · Answer 2 · 2019-04-24T01:33:00

You did not provide enough details about your issue, especially about In and Out entries... Do they can exceed a day?

If In-Out entries do not exceeed a day, you have to create a pair of In-Out entries for each user per day. Then you have to calculate a time in minutes. You can use DATEADD[^] function to achieve that: DATEDIFF(n, OutTime, InTime)

Finally, you have to sum total time per user.

EmpID     IN                        OUT                      StayTime(Minutes)
1001      2019-03-31 08:30:00 AM    2019-03-31 04:30:00 PM   480
1002      2019-03-31 08:30:00 AM    2019-03-31 03:45:00 PM   435

**Nirav Prabtani** · Answer 3 · 2019-04-05T02:33:00

Solution 1

You should use DATEDIFF[^] function to achieve your need.

You have to find maximum OUT time and minimum IN time user wise, then you may use DATEDIFF function like this

SQL

SELECT DATEDIFF(MINUTE, ENTRY_TIME_OUT, ENTRY_TIME_IN) AS DateDiff;

This will return the total time with respect to IN/OUT time in minutes.

Posted 5-Apr-19 2:33am

Nirav Prabtani

Comments

Maciej Los 24-Apr-19 7:34am

Upvoted to eliminate unfair down-vote!

Nirav Prabtani 25-Apr-19 0:38am

Thanks,I appreciate

I frequently experience that, someone is intentionally doing that in all of my recent answer.

Any way, I wish he/she get well soon :)

Shraddha_Patel · Answer 4 · 2019-04-21T22:11:00

Solution 3

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), '03:30:00')

Posted 21-Apr-19 22:11pm

Shraddha_Patel

How to add datetime in SQL

4 solutions

Solution 2

Solution 4

Solution 1

Solution 3

Add your solution here

Preview 0