在预言机中应用系统日期

Question

如何在查询中应用系统日期？

SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
date_time, l.nreaderidn, r.sname, 
CASE 
WHEN l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = 'LOCKER' and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT' 
WHEN l.nreaderidn  IN (SELECT nreaderidn FROM tb_reader where sdescription = 'LOCKER' and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' 
END logtype 
FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
WHERE l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = 'LOCKER') 
AND NDATETIME >= ((TO_DATE ('2020-01-27' || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
AND ndatetime <= ((TO_DATE ('2020-01-28' || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS PM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
AND l.nuserid = u.suserid 
AND l.nreaderidn = r.nreaderidn                           
ORDER BY 2, 4

在这一部分中，我想添加系统日期

>= ((TO_DATE ('2020-01-27' || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM') )

基于我搜索系统应该使用的内容，但我正在为格式而苦苦挣扎。

我尝试过使用它，但仍然没有运气。

SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
date_time, l.nreaderidn, r.sname, 
CASE 
WHEN l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = 'LOCKER' and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT' 
WHEN l.nreaderidn  IN (SELECT nreaderidn FROM tb_reader where sdescription = 'LOCKER' and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' 
END logtype 
FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
WHERE l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = 'LOCKER') 
AND NDATETIME >= ((TO_DATE (sysdate || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
AND ndatetime <= ((TO_DATE (sysdate || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS PM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
AND l.nuserid = u.suserid 
AND l.nreaderidn = r.nreaderidn                           
ORDER BY 2, 4

Answer 1

>SYSDATE是一个返回DATE数据类型的函数，因此您不会TO_DATE它。只需将其用作

where ndatetime < sysdate

(假设ndatetime列也是DATE(。

Answer 2

使用 trunc funtion 和 sysdate 求解

我的最后一个问题是这个

SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
date_time, l.nreaderidn, r.sname, 
CASE WHEN l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = cur_rec.SDESCRIPTION and upper(sname) like '%' || upper('OUT') || '%') THEN 'OUT' 
WHEN l.nreaderidn  IN (SELECT nreaderidn FROM tb_reader where sdescription = cur_rec.SDESCRIPTION and upper(sname) like '%' || upper('IN') || '%') THEN 'IN' END logtype 
FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
WHERE 
l.nreaderidn IN (SELECT nreaderidn FROM tb_reader where sdescription = cur_rec.SDESCRIPTION) 
AND NDATETIME >= (trunc(sysdate -1) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
AND ndatetime <= (trunc(sysdate) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
AND l.nuserid = u.suserid 
AND l.nreaderidn = r.nreaderidn                           
ORDER BY 2, 4;