Select Conditional Date (Oracle)
I have a problem that hasn't been resolved for days, I have searched and answered many times on the answer, it seems that the structure data for the datetime in my database is so strange. I read this post: Select from table knowing only date without time (ORACLE) but that doesn't work for me. Ok, let's start with the problem,
Im using ODBC connecting to get data in an ORACLE database for troubleshooting.
When I select with a simple query like this:
select Create_Date from HPD_Help_Desk_SLA
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')
Result:
Warning: odbc_exec(): SQL error: [AR System ODBC Driver]Column not found: ., SQL state S0000 in SQLExecDirect in....
and I am trying 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 it doesn't show anything
Any ideas for this? Thanks to
source to share
My comment "updated" to the correct answer as requested:
Your error message indicates that you are using: [AR System ODBC Driver]
.
You are using an ODBC data source that connects to your AR system, not directly to an Oracle database. This would mean that the query syntax should be whichever AR System syntax allows (although the Oracle database runs "under" AR System.) So you get an error using TO_DATE
.
You need to either define the correct syntax for date queries in the AR system.
Or you can switch to the Oracle ODBC driver and data source, but then you will probably need different credentials to connect as you will bypass your AR system and access the database directly. This could be a security issue in your setup.
I think you should ask someone who knows AR System, not so much Oracle knowledge.
EDIT:
Google for "AR System" "Oracle"
gives this link as a first hit:
This reference guide shows how an AR system can work in several different relational databases, one of which is Oracle. Page 23 shows that the timestamp of the AR system is stored in Oracle as a number. I'm pretty sure this means that when you are using the ODBC AR System driver, you need to write SQL in the ODBC call in AR System syntax. The AR ODBC system driver then overwrites this with the syntax required by all relational DBMSs on which AR is installed.
Thus, this supports my suggestion to ask AR System developers, not Oracle developers.
source to share
You are comparing a date with a literal one. Use to_date to convert the literal to date.
where Create_Date = to_date( '2014-08-04', 'YYYY-MM-DD')
trunc
will truncate the temporary part. The query will return all rows for this date. For a date range, if you're interested in a fraction of the time, remove the trunc.
If the problem is related to ODBC
, then even if it is fixed, you will get an error related to the datetime format (data type ii DATE
). It depends on yours 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 the implicit conversion will execute the following request.
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 haven't seen the OP is already in use TO_DATE
and hence the issue is not the date format.
An TO_DATE
error appears when using . It is very likely that the database is not Oracle
, and therefore the function is TO_DATE
not recognized in the particular database.
Check if it works . select * from v$version
source to share