选择最大或最晚日期的问题 [英] Problem to select max or latest date
问题描述
我想根据最新日期显示一些细节。但是,我有问题。它说Oracle.DataAccess.Client.OracleException:ORA-00923:在预期的地方找不到FROM关键字
我尝试过:
以下是查询:
SELECT DISTINCT CCSMASTERLISTREVNO,CCSREVCONTENT,CCSPREPAREDREV,
CCSREVEFFECTIVATEATE
来自CCS2_TBL_MASTERLIST
在哪里CCSEQUIPMENTDPMT =:DPMT
和CCSMASTERLISTREVNO< =:REVNO
和CCSREVEFFECTIVEDATE =
(SELECT MAX(TO_CHAR(CCSREVEFFECTIVEDATE,'dd / MM / yyyy'))
来自CCS2_TBL_MASTERLIST)
ORDER BY CCSMASTERLISTREVNO DESC;
( SELECT MAX(TO_CHAR(CCSREVEFFECTIVEDATE,' dd / MM / yyyy'))< - extra)here
FROM CCS2_TBL_MASTERLIST)< - 此 end SELECT
你有一个上面第一行的额外结束括号。
为什么要将日期转换为字符串以找到最大值?
< blockquote>添加到理查德所说的内容:当您将日期比较为字符串时,您使用字符串比较。这意味着两个字符串逐字符进行检查,整个比较的结果基于找到的第一个不同的字符对。
在您的情况下,这意味着11/01/2018之前1977年12月1日和01/01/2048之后
你必须存储和比较日期作为日期,而不是字符串 - 否则你会得到一些非常奇怪的看似结果。
Hi, I want to display some details based on the latest date. But, I have problem with it. It say that "Oracle.DataAccess.Client.OracleException: ORA-00923: FROM keyword not found where expected"
What I have tried:
Below are the query:
SELECT DISTINCT CCSMASTERLISTREVNO, CCSREVCONTENT, CCSPREPAREDREV,
CCSREVEFFECTIVEDATE
FROM CCS2_TBL_MASTERLIST
WHERE CCSEQUIPMENTDPMT = :DPMT
AND CCSMASTERLISTREVNO <= :REVNO
AND CCSREVEFFECTIVEDATE =
( SELECT MAX(TO_CHAR(CCSREVEFFECTIVEDATE,'dd/MM/yyyy'))
FROM CCS2_TBL_MASTERLIST )
ORDER BY CCSMASTERLISTREVNO DESC;
( SELECT MAX(TO_CHAR(CCSREVEFFECTIVEDATE,'dd/MM/yyyy'))<- extra ) here FROM CCS2_TBL_MASTERLIST ) <- this is the end of the SELECT
You have an extra closing parenthesis on the first line above.
And why are you converting your dates to character strings to find the maximum value?
To add to what Richard says: when you compare dates as strings, you use a string comparison. Which means that the two strings are examined character by character and the result of the whole comparison is based on the first different pair of characters found.
Which in your case means that 11/01/2018 comes before 12/01/1977 and after 01/01/2048
You must store and compare dates as dates, not strings - or you will get some very odd seeming results.
这篇关于选择最大或最晚日期的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!