在 Oracle 中获取前一天的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29685205/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get date of the previous day in Oracle
提问by Daniel Ramirez
I need to bring the day immediately preceding date in Oracle using a truncate but not how. He was using the following line but bring me some records for the current day of execution and should not be. Neceisto only the previous day; investigation found the truncate with dates in Oracle but not how to use it.
我需要使用截断而不是如何将 Oracle 中紧接在日期的前一天。他正在使用以下行，但给我带来了当天执行的一些记录，不应该。Neceisto 仅在前一天；调查发现在 Oracle 中截断了日期，但没有发现如何使用它。
and fnxs.FECHA_INGRESO BETWEEN (TO_CHAR (SYSDATE-1, 'DD-MON-YY')) AND (TO_CHAR (SYSDATE, 'DD-MON-YY'));
I appreciate your help
我感谢您的帮助
回答by Gerrat
Using BETWEENwith dates in Oracle is generally a bad idea. I see it all the time, and most of the time people get it wrong (like in the accepted answer above). Even when they fully understand that the two dates are included, they still make logical errors because they forget about timestamps.
BETWEEN在 Oracle 中使用日期通常是一个坏主意。我一直看到它，而且大多数时候人们都弄错了（就像上面接受的答案一样）。即使他们完全理解包含两个日期，他们仍然会犯逻辑错误，因为他们忘记了时间戳。
The OP is asking for yesterday dates. The following sql shows that today falls within "BETWEEN TRUNC( SYSDATE ) - 1 AND TRUNC( SYSDATE )"
OP 要求提供昨天的日期。下面的sql显示今天属于“BETWEEN TRUNC(SYSDATE) - 1 AND TRUNC(SYSDATE)”
with adate as (
select trunc(sysdate) today from dual
) select today from adate where today between trunc(sysdate) -1
and trunc(sysdate);
16-Apr-15 00:00:00 [returns the record for today]
16-Apr-15 00:00:00 [返回今天的记录]
I find it easier to be correct with dates when you're more explicit about the end points:
我发现当您对终点更加明确时，更容易更正日期：
SELECT * from your_table
WHERE fnxs.FECHA_INGRESO >= TRUMC(SYSDATE) - 1
AND fnxs.FECHA_INGRESO < TRUNC(SYSDATE);
Upon looking closer, the OP's date-like column mightbe a VARCHAR2 (could still be a date that was implicitly cast in the comparison he gave). If it is a VARCHAR, then it needs to be converted first (using an appropriate format string):
仔细观察后，OP 的类似日期的列可能是 VARCHAR2（仍然可能是在他给出的比较中隐式转换的日期）。如果是 VARCHAR，则需要先对其进行转换（使用适当的格式字符串）：
SELECT * FROM your_table
WHERE TO_DATE(fnxs.FECHA_INGRESO, 'DD-MON-YY') >= TRUMC(SYSDATE) - 1
AND TO_DATE(fnxs.FECHA_INGRESO, 'DD-MON-YY') < TRUNC(SYSDATE);
回答by MT0
Assuming your column is of type DATE
假设您的列是类型 DATE
SELECT *
FROM TABLE_NAME
WHERE FECHA_INGRESO BETWEEN TRUNC( SYSDATE ) - 1
AND TRUNC( SYSDATE );
If it is a character string then:
如果是字符串，则：
SELECT *
FROM TABLE_NAME
WHERE TO_DATE( FECHA_INGRESO, 'DD-MON-YY' )
BETWEEN TRUNC( SYSDATE ) - 1
AND TRUNC( SYSDATE );
