Select date time with condition (Oracle)

Question

I have problem that not solved for many days, i've been searching and follow answer in many question, it seems like the structure data for the datetime in my database so strange. i've read this post : Select from table by knowing only date without time (ORACLE) but it's not work on me. Okay let's start for the problem,

Im using ODBC connect to fetch data in ORACLE Database for Remedy Client.

When I select with simple query like this :

select Create_Date from HPD_Help_Desk_SLA 

it result :

Create_Date
2014-07-01 05:27:02.000
2014-07-02 05:27:02.000

but when i try this query :

select Create_Date from HPD_Help_Desk_SLA where Create_Date >= TO_DATE('2014-05-03', 'YYYY-MM-DD') AND Create_Date <  TO_DATE('2014-08-04', 'YYYY-MM-DD')

it result :

Warning: odbc_exec(): SQL error: [AR System ODBC Driver]Column not found: ., SQL state S0000 in SQLExecDirect in....

and I try for this simple query :

select Create_Date from HPD_Help_Desk_SLA where Create_Date <= '2014-08-04'

and

select Create_Date from HPD_Help_Desk_SLA where Create_Date like '2014-07-01%`'

and this not showing anything

Any ideas for this? Thanks

Answer 1

My comment "upgraded" to a proper answer upon request:

Your error message shows you are using: [AR System ODBC Driver].

You are using an ODBC datasource that connects to your AR System and not directly to the Oracle database. Which would mean that the syntax of the query would have to be whichever syntax AR System allows (even though Oracle database runs "underneath" AR System.) Therefore you get error using TO_DATE.

You either need to figure out proper syntax for date queries in AR System.

Or you can switch to an Oracle ODBC driver and datasource, but then you probably need different connection credentials as you would be bypassing your AR System and going to the database directly. That may be a security issue in your setup.

I think you should ask someone with AR System knowledge, not so much Oracle knowledge.

EDIT:

Google for "AR System" "Oracle" gives this reference as first hit:

http://www.unc.edu/remedy/clients/7.0.1/BMC%20Remedy%20AR%20System%20Server%207-0-01/Database-Ref-700.pdf

That reference manual shows how the AR System can run on several different relational databases, Oracle being one of them. Page 23 shows that an AR System "timestamp" is saved in Oracle as a number. I am almost sure that this means that when you use the AR System ODBC driver, you need to write the SQL in the ODBC call in AR System syntax. Then the AR System ODBC driver rewrites this to the syntax needed in whatever relational database the AR System is installed upon.

So it reinforces my suggestion to ask AR System developers rather than Oracle developers.

Answer 2

You are comparing date with literal. Use to_date to convert the literal to date.

where Create_Date = to_date( '2014-08-04', 'YYYY-MM-DD')

trunc will truncate the time portion. The query would return all the rows for that date. For the date range, if you are concerned for the time portion, remove trunc.

If the issue is related to ODBC, then even if it is fixed, you would get an error related to datetime format(ii the data type is DATE). It depends on your NLS_DATE_FORMAT.

For example,

SQL> SELECT empno, ename, hiredate FROM emp WHERE hiredate <='20/02/1981';
SELECT empno, ename, hiredate FROM emp WHERE hiredate <='20/02/1981'
                                                        *
ERROR at line 1:
ORA-01843: not a valid month


SQL>

But, with an implicit conversion, the next query would execute.

SQL> SELECT empno, ename, hiredate FROM emp WHERE hiredate <='20-FEB-1981';

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81

SQL>

So, to be safe,

SQL> SELECT empno, ename, hiredate FROM emp WHERE hiredate <=to_date('20-FEB-1981','DD-MON-YYYY');

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81

SQL>

Update I didn't see OP already used TO_DATE and thus the issue doesn't seem to be date format.

When using TO_DATE, there is an error thrown. It is highly likely that the database is not Oracle, and thus the TO_DATE function is not recognized in the particular database.

Check if select * from v$version works for you.