Return Number from Oracle Select Statement After Parsing Date

Oracle String Date to date getting back last 7 days

SELECT TO_DATE(START_UTC_DAY, 'YYYYMMDD')
FROM SOURCETABLE
WHERE TO_DATE(START_UTC_DAY, 'YYYYMMDD') > SysDate - 7

TO_DATE() in the WHERE clause converts the string value with the given format model to a DATE type before comparing it to another DATE value thus avoiding the implicit conversion which caused you the issue. See comments for a better variation.

The message here is avoid implicit conversions.

Explanation:

The error was due to implicit conversion in the WHERE clause, a comparison between two values of different data types.

2.2.8.2 Implicit Data Conversion

• When comparing a character value with a DATE value, Oracle
  
  converts the character data to DATE.

During the conversion, the character value/string literal is evaluated against a date format model that depends on the following setting.

select name, value from v$parameter where name = 'nls_date_format';

In my database it returns,

NAME                 VALUE               
-------------------- --------------------
nls_date_format      DD-MON-YY 

This setting can be changed at the level of a session and that will produce various results as shown below.

Here are some tests to further explain the behavior using SQL Developer using a new session.

SQL> show parameter nls_date_format;
NAME            TYPE   VALUE     
--------------- ------ --------- 
nls_date_format string DD-MON-YY 

SQL> 
select str from 
(select '20200412' as str from dual)
where str <> sysdate;

...
Error report -
ORA-01861: literal does not match format string

SQL> 
alter session set nls_date_format = 'ddmmyyyy';

Session altered.

SQL> 
SQL> show parameter nls_date_format;
NAME            TYPE   VALUE    
--------------- ------ -------- 
nls_date_format string ddmmyyyy 

SQL> 
select str from 
(select '20200412' as str from dual)
where str <> sysdate;

...
Error report -
ORA-01843: not a valid month

SQL> 
alter session set nls_date_format = 'fxyyyy-mm-dd';

Session altered.

SQL> 
SQL> show parameter nls_date_format;
NAME            TYPE   VALUE        
--------------- ------ ------------ 
nls_date_format string fxyyyy-mm-dd 

SQL> 
select str from 
(select '20200412' as str from dual)
where str <> sysdate;

...
Error report -
ORA-01861: literal does not match format string

SQL> 
alter session set nls_date_format = 'yyyymmdd';

Session altered.

SQL> 
SQL> show parameter nls_date_format;
NAME            TYPE   VALUE    
--------------- ------ -------- 
nls_date_format string yyyymmdd 

SQL> 
select str from 
(select '20200412' as str from dual)
where str <> sysdate;

STR     
--------
20200412

As shown above, the same query ends up producing different "results" depending on the nls_date_format setting.

How to parse a string to a date, when the string has a character in it?

You have to put the T in double quotes, like YYYY-MM-DD"T"HH:MI:SS.

select to_date( '2015-07-09T12:22:29', 'YYYY-MM-DD"T"HH:MI:SS' ) from dual;

TO_DATE('
---------
09-JUL-15

Here's the relevant documentation. (Thanks Alex)

Oracle - Date format validation

This is an anonymous PL/SQL block that takes your date string as a bind variable:

DECLARE
  dt DATE;
BEGIN
  BEGIN
    dt := TO_DATE( :your_date_string, 'MM/DD/YYYY' );
  EXCEPTION
    WHEN OTHERS THEN
      raise_application_error( -20000, 'Invalid date format' );
  END;

  -- do stuff with your date.
  NULL;
END;
/

If you just want it as a procedure or function then add the correct signature.

Get month name from date in Oracle

select to_char(sysdate, 'Month') from dual

in your example will be:

select to_char(to_date('15-11-2010', 'DD-MM-YYYY'), 'Month') from dual

SQL query for extracting year from a date

Edit: due to post-tag 'oracle', the first two queries become irrelevant, leaving 3rd query for oracle.

For MySQL:

SELECT YEAR(ASOFDATE) FROM PASOFDATE

Editted:
In anycase if your date is a String, let's convert it into a proper date format. And select the year out of it.

SELECT YEAR(STR_TO_DATE(ASOFDATE, '%d-%b-%Y')) FROM PSASOFDATE

Since you are trying Toad, can you check the following code:

For Oracle:

SELECT EXTRACT (TO_DATE(YEAR, 'MM/DD/YY') FROM ASOFDATE) FROM PSASOFDATE;

Reference:

How to validate dates in a txt file using Oracle SQL?

you can create your own function, if smth is wrong with dates it returns null

create or replace function my_to_date(dt varchar2) return date as
  ldt date;
begin
  ldt := to_date(dt, 'MM/DD/YYYY');
  return ldt;
exception when others then return null;

end;

select my_to_date('01/01/2012') from dual
union all
select my_to_date('33/01/2012') from dual

    MY_TO_DATE('01/01/2012')
1   01/01/2012
2   

or you need create script that will check your data after import

but maybe it possible to you make the check during import process? via to_char(to_date(...)...) convert ? if smth is wrong with your dates - sqlldr reject the record

sample record
7782, "Clark", "Manager", 7839,06/09/1981, 2572.50,, 10:101

sample control file

LOAD DATA 
CHARACTERSET utf16
BYTEORDER little
INFILE ulcase11.dat
REPLACE
INTO TABLE EMP
FIELDS TERMINATED BY X'002c' OPTIONALLY ENCLOSED BY X'0022'
(empno integer external (5), ename, job, mgr,
 hiredate DATE(20) "to_char(to_date(:HIREDATE, 'MM/DD/YYYY'),'MM/DD/YYYY')",
 sal, comm,
 deptno   CHAR(5) TERMINATED BY ":",
 projno,
 loadseq  SEQUENCE(MAX,1) )    

Oracle SQL SELECT DATE from DATETIME field

TO_DATE (REPORTDATE, 'DD.MON.YYYY')

This makes no sense. You are converting a date into a date again. You use TO_DATE to convert a string literal into DATE.

I want result to return only 29.10.2013

You could use TRUNC to truncate the time element. If you want to use this value for DATE calculations, you could use it directly.

For example,

SQL> select TRUNC(SYSDATE) dt FROM DUAL;

DT
---------
12-MAR-15

To display in a particular format, you could use TO_CHAR and proper FORMAT MASK.

SQL> SELECT to_char(SYSDATE, 'DD.MM.YYYY') dt from dual;

DT
----------
12.03.2015

SQL>

Valid Date Checks in Oracle

Yes, if you know the format and with little plsql.

Let's say you have the date in format 'yyyy-mon-dd hh24:mi:ss'.

create function test_date(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'yyyy-mon-dd hh24:mi:ss') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;

Now you can:

select your_date_col, test_date(your_date_col)
from your_table;

how to parse date n java to oracle/sql query?

for sysdate-?
the ? parameter is to be set object of java.sql.Date class instead of java.util.Date class.
java.sql.Date inherits java.util.Date.
java.sql.Date(long time)
here long is number of milliseconds lapsed since jan1,1979.

GregorianCalendar gc=new GregorianCalendar(2000, 25, 2);

java.util.Date dt=gc.getTime();
long lg=dt.getTime();
java.sql.Date sqldt=new java.sql.Date(lg);
'

pass sqldt object in setDate() method of preparedstatement instance.

hope this works

---

alkaramansari1@gmail.com

---

Related Topics