Link to home
Start Free TrialLog in
Avatar of jasmeen kaur
jasmeen kaur

asked on

i am getting an error : ORA-00933: SQL command not properly ended

on running the below query, i am getting the error ORA-00933: SQL command not properly ended

select to_char(cast(A.xx as date),'dd/mm/yyyy')as clnt_date_of_birth from t2 A join
t2 B on A.clnt_id=B.exrf_table_id
where B.column='1004620399';
Avatar of von Axl
von Axl
Flag of Slovenia image

What is B.column?
Assuming that those are not the real table and column names, there is actually no issue with that query, other that you cannot create a table with a column named COLUMN easily.  The sample is a self join and if you create the table this way:
CREATE TABLE t2 
  ( 
     xx            VARCHAR2(10), 
     clnt_id       NUMBER, 
     exrf_table_id NUMBER, 
     col           VARCHAR2(20) 
  ); 

Open in new window

Then run the query:
SELECT To_char(Cast(A.xx AS DATE), 'dd/mm/yyyy')AS clnt_date_of_birth 
FROM   t2 A 
       JOIN t2 B 
         ON A.clnt_id = B.exrf_table_id 
WHERE  B.col = '1004620399'; 

Open in new window

You get no errors.

So, you need to post the real query with the real column and table names.  Something may be odd with one of the identifiers.

Also, why are you casting A.XX to a date?  One would assume that the data type is already date.  Casting a DATE to a DATE is pretty useless.

If you really had a column with the name COLUMN, you can use these commands to generate your sample:
CREATE TABLE t2 
  ( 
     xx            VARCHAR2(10), 
     clnt_id       NUMBER, 
     exrf_table_id NUMBER, 
     "COLUMN"      VARCHAR2(20) 
  ); 
SELECT To_char(Cast(A.xx AS DATE), 'dd/mm/yyyy')AS clnt_date_of_birth 
FROM   t2 A 
       join t2 B 
         ON A.clnt_id = B.exrf_table_id 
WHERE  B."COLUMN" = '1004620399'; 

Open in new window

Still won't generate the error.  Even if you leave the quotes off the select, it will generate an error, but not the one you are getting.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jasmeen kaur
jasmeen kaur

ASKER

the column had to be converted to date since it was not in date data type.
If the column is not a DATE datatype, then I wouldn't convert it to a date.  If you have a bad date, your query will fail on the conversion.  You are better off doing string manipulation to get it into the format you want.

If you really want to convert it to a DATE, you should specify the date format.  Never rely on implicit conversion.  It is way too easy for someone to change their default date format and break your query.