Oracle String Date to date getting back last 7 days

1
votes

I have very large data set I am querying. Over 90 million rows. I am trying to limit the data by the last 7 days but am having trouble with the Oracle syntax. The DB is Oracle 11g

The date field is called START_UTC_DAY it is a string and looks like 20200414

I have tried

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

I get the ORA-01861 Literal does not match format string

Forgive my ignorance, my SQL is rudimentary and I am learning as I go.

3
sqloracledateoracle11g
What is the data type of column START_UTC_DAY? - Wernfried Domscheit
DESC SOURCETABLE; and paste the output, or at least the line which has START_UTC_DAY showing its data type. - Lalit Kumar B
Why are you storing date values in a varchar column? That is a really, really bad idea - a_horse_with_no_name
@a_horse_with_no_name I wish I knew. There are three rather large Oracle DB's we currently have that all data is stored as VARCHAR, while several Postgres and MySQL DB's all have clearly defined data types. Only excuse I can see is these are mammoth systems built by a third party many years ago. We got what we paid for and it has worked for over a decade. Reason I keep being brought into it, seems they keep asking me to update/revise processes from this archaic DB to our soon to be cloud data lake and DB's . A lucky position as I get to sharpen my SQL and AWS tool set! - General Douglas MacArthur

3 Answers

3
votes
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.

1
votes

The date field is called START_UTC_DAY it is a string and looks like 20200414

When you say date looks like 20200414 doesn't mean it's a string, it just means that your locale-specific NLS settings are set to display the date in that format. But it isn't actually stored in any format.

I get the ORA-01861 Literal does not match format string

That's because you are using TO_DATE on a date, therefore forcing Oracle to convert the DATE into string based on the NLS dependent format, and then converting back to date using the format you mentioned. This causes ORA-01861 Literal does not match format string error.

Never use TO_DATE on a date column, use TO_CHAR to display it in the format you desire:

SELECT TO_CHAR(START_UTC_DAY, 'YYYYMMDD')
FROM SOURCETABLE
WHERE START_UTC_DAY > SYSDATE -7;

Also, remember DATE also has time portion up to seconds. In case you only want the date portion without time, you need to use TRUNC(SYSDATE).

0
votes

I think you might want TO_CHAR() rather than TO_DATE():

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

Note that sysdate has a time component. So, you might want: trunc(sysdate) - interval '7' day (I prefer the explicit interval syntax).