SQL 另一个 SELECT 中的 SYSDATE FROM DUAL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22014413/
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
SYSDATE FROM DUAL in another SELECT
提问by astrak
I have problems with time in my select. Time in DB is different than system time so I guess I need to use SELECT SYSDATE FROM DUALbut it doesn't work in another select. I use PL/SQL Developer.
我的选择有时间问题。DB 中的时间与系统时间不同，所以我想我需要使用SELECT SYSDATE FROM DUAL但它在另一个选择中不起作用。我使用 PL/SQL 开发人员。
SELECT t.something1,
ROUND((TRUNC(SELECT SYSDATE FROM DUAL) - 1 + 2),1) AS NAME
FROM customers t
WHERE t.something1 = ROUND((TRUNC(SELECT SYSDATE FROM DUAL) - 1 + 2),1);
I have also tried to declare new variable but I always get error message.
我也尝试声明新变量，但我总是收到错误消息。
DECLARE
day DATE
SELECT SYSDATE
INTO day
FROM DUAL
SELECT t.something1,
ROUND((TRUNC(day)- 1 + 2),1) AS NAME
FROM customers t
WHERE t.something1 = ROUND((TRUNC(day) - 1 + 2),1);
回答by tofcoder
You must use SELECT SYSDATE FROM DUALto get SYSDATEonly if you have no table to select from. This is because a SELECTwithout a FROMis not valid with Oracle, so when you want to use a SQL function and you have no table, you have to use DUAL, which is a special one-row one-column (named DUMMY) table. The only row has the value X.
仅当您没有可供选择的表时，才必须使用SELECT SYSDATE FROM DUALget SYSDATE。这是因为 aSELECT不带 aFROM对 Oracle 无效，所以当你想使用 SQL 函数而你没有表时，你必须使用DUAL，它是一个特殊的单行单列（命名DUMMY）表。唯一的行具有值X。
回答by Yagnesh Agola
ROUND(number)function will only work with numberdata type. It will not work with the datetype.
If your t.something1column data type is date than you can't used round() function (you can directly compare date with date), if it not and is number than you will use round() and you need to convert your sysdate into number and compare.
ROUND(number)函数仅适用于number数据类型。它不适用于该date类型。
如果您的t.something1列数据类型是日期，则不能使用 round() 函数（您可以直接将日期与日期进行比较），如果不是并且是数字，则您将使用 round() 并且您需要将您的系统日期转换为数字和相比。
As per my understanding you do something like below :
根据我的理解，您可以执行以下操作：
SELECT t.something1,
ROUND(to_number(to_char(sysdate+1,'DDMMYYYY')),1) AS NAME
FROM customers t
WHERE t.something1 = ROUND(to_number(to_char(sysdate+1,'DDMMYYYY')),1);
May this will help you.
愿这对你有帮助。
回答by Gordon Linoff
You don't need the subquery:
您不需要子查询：
SELECT t.something1,
ROUND((TRUNC(SYSDATE) - 1 + 2),1) AS NAME
FROM customers t
WHERE t.something1 = ROUND((TRUNC(SYSDATE) - 1 + 2),1);
Does this fix your problem?
这能解决您的问题吗？
