Problem to select max or latest date

Question

0.00/5 (No votes)

See more:

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;

Posted 17-Apr-18 20:53pm

Member 13746151

Updated 17-Apr-18 21:13pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2018-04-17T21:05:00

Solution 1

SQL

( 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?

Posted 17-Apr-18 21:05pm

Richard MacCutchan

OriginalGriff · Answer 2 · 2018-04-17T21:14:00

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.