如何在SQL中添加日期时间 [英] How to add datetime in SQL

问题描述

我需要在员工出勤时间表上加上时间。



出勤表字段

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

现在我需要总计给定的进出时间员工怎么做？



我的尝试：



我正在搜索goolge结果是

使用DateADD函数

但我不知道请帮助我Sir

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

推荐答案

你不能添加两个DateTime值，任何m你可以添加两个电话号码 - 结果不是任何明智或有用的。你可以减去两个DateTimes，它们给你一个时间跨度，可以用几种方式表达，分钟就是其中之一。



所以从匹配开始您的员工的IN和OUT时间 - 在这个阶段对您来说可能已经足够困难了 - 然后看看减去它们以找出它们工作了多长时间。如果有，您可以开始汇总数据以获得总数。

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.

您没有提供有关您的问题的足够详细信息，尤其是关于 In 和输出条目...它们可以超过一天吗？



如果 In-Out 条目不会超过一天，您必须为每个用户每天创建一对 In-Out 条目。然后你必须以分钟计算一个时间。您可以使用 DATEADD [ ^ ]功能实现： DATEDIFF（n，OutTime，InTime）



最后，你必须总计每个用户的总时间。

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

您应该使用 DATEDIFF [ ^ ]功能可满足您的需求。



你必须找到最大的OUT时间和最小值m用户明智的时间，那么你可以使用像这样的DATEDIFF函数

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

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

这将以分钟为单位返回相对于IN / OUT时间的总时间。

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

这篇关于如何在SQL中添加日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！