日期时间字符串到日期,时间和时间 [英] DateTime String to Date, In Time and Out Time
本文介绍了日期时间字符串到日期,时间和时间的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的出席记录表
+ --------------- - + --------- + ----------------------- +
| NIP | Nama | Date_Time |
+ ---------------- + --------- + ------------------ ----- +
| 050803075201 | Supomo | 2013-02-20 07:45:57 |
| 050803075201 | Supomo | 2013-02-20 17:24:13 |
| 050803075201 | Supomo | 2013-02-21 07:53:40 |
| 050803075201 | Supomo | 2013-02-21 17:31:57 |
| 050803075200 |特古| 2013-02-21 20:31:02 |
| 050803075200 |特古| 2013-02-20 18:18:07 |
+ ---------------- + --------- + ------------------ ----- +
Date_Time
是以字符串格式。
然后我想让它像这样的表格:
+ ---------------- + --------- + ------------- + ---- --------- + ------------- +
| NIP | Nama |日期|在|中出| |
+ ---------------- + --------- + ------------- + ---- --------- + ------------- +
| 050803075200 |特古| 2013-02-21 | | 18:18:07 |
| 050803075200 |特古| 2013-02-20 | | 20:31:02 |
| 050803075201 | Supomo | 2013-02-20 | 07:45:57 | 17:24:13 |
| 050803075201 | Supomo | 2013-02-21 | 07:53:40 | 17:31:57 |
+ ---------------- + --------- + ------------- + ---- --------- + ------------- +
以下是Query
select NIP,
Nama,
DATE_FORMAT(Date_Time,'%Y-%m - %d')as date,
Case Min(Date_Time)
当Max(Date_Time)
Then''
DATE_FORMAT(Date_Time,'%H:%i:% s')
作为InTime,
DATE_FORMAT(max(Date_Time),'%H:%i:%s')作为OutTime
从Attd
结束通过NIP,
Nama,
DATE_FORMAT(Date_Time,'%Y-%m-%d')
I have attendance record table like this
+----------------+---------+-----------------------+
| NIP | Nama | Date_Time |
+----------------+---------+-----------------------+
| 050803075201 | Supomo | 2013-02-20 07:45:57 |
| 050803075201 | Supomo | 2013-02-20 17:24:13 |
| 050803075201 | Supomo | 2013-02-21 07:53:40 |
| 050803075201 | Supomo | 2013-02-21 17:31:57 |
| 050803075200 | Teguh | 2013-02-21 20:31:02 |
| 050803075200 | Teguh | 2013-02-20 18:18:07 |
+----------------+---------+-----------------------+
Date_Time
is in string format.
And then I want to make it like this table:
+----------------+---------+-------------+-------------+-------------+
| NIP | Nama | Date | In | Out |
+----------------+---------+-------------+-------------+-------------+
| 050803075200 | Teguh | 2013-02-21 | | 18:18:07 |
| 050803075200 | Teguh | 2013-02-20 | | 20:31:02 |
| 050803075201 | Supomo | 2013-02-20 | 07:45:57 | 17:24:13 |
| 050803075201 | Supomo | 2013-02-21 | 07:53:40 | 17:31:57 |
+----------------+---------+-------------+-------------+-------------+
What query will do this?
解决方案
Below is the Query
select NIP,
Nama,
DATE_FORMAT(Date_Time, '%Y-%m-%d') as date,
Case Min(Date_Time)
When Max(Date_Time)
Then ''
Else DATE_FORMAT(Date_Time, '%H:%i:%s')
End as InTime,
DATE_FORMAT(max(Date_Time), '%H:%i:%s') as OutTime
from Attd
Group By NIP,
Nama,
DATE_FORMAT(Date_Time, '%Y-%m-%d')
这篇关于日期时间字符串到日期,时间和时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文