CODEWITHSUNDEEP
oracle-databaseplsql
user13541818
user13541818

Reputation:

Format string does't match

I have one problem in my query and I don't see where the problem is. So the problem is when I run the query I get error like

ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

So far my query is here

SELECT rp.* 
FROM responsible_persons  rp
LEFT JOIN projects  p ON p.ProjectID = rp.ProjectID
WHERE rp.UserID = 195
AND (SYSDATE BETWEEN p.StartDate AND p.EndDate)

So far I try to convert StartDate and EndDate to_date() but still I have problem. What is wrong here ? Where I made mistake ?

Upvotes: 0

Views: 369

Answers (2)

Stilgar
Stilgar

Reputation: 461

You really shouldn't store dates as strings. Now, having said that, you would want to change your code to

AND (SYSDATE BETWEEN TO_DATE(p.StartDate,'YYYY-MM-DD') AND TO_DATE(p.EndDate,'YYYY-MM-DD'))

But understand that if p.EndDate is '2020-08-28' and SYSDATE is 2020-08-28 07:49:30, then you won't get a match because your date strings don't contain the time component that SYSDATE contains. So you might also want to strip the time component off of SYSDATE with this

TRUNC(SYSDATE)

As suggested by Roberto, you could manipulate the NLS_DATE_FORMAT for your session to get past this problem. But if you have other dates stored as strings and they aren't in the same format, then you've just pushed another problem onto the stack.

Upvotes: 1

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

Try to set the NLS_DATE_FORMAT in your session to the values in your table, that sysdate can get the same format as the data you have there:

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD' ;

SELECT rp.* 
FROM responsible_persons  rp
LEFT JOIN projects  p ON p.ProjectID = rp.ProjectID
WHERE rp.UserID = 195
AND (SYSDATE BETWEEN p.StartDate AND p.EndDate)

An example

SQL> create table t ( c1 varchar2(20) , c2 varchar2(20) ) ;

Table created.

SQL> insert into t values ( '22.01.2020 10:00:00' , '22.01.2020 21:00:00' ) ;

1 row created.

SQL> select * from t where ( sysdate between c1 and c2 ) ;
select * from t where ( sysdate between c1 and c2 )
                                        *
ERROR at line 1:
ORA-01843: not a valid month


SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss'  ;

Session altered.

SQL> select * from t where ( sysdate between c1 and c2 ) ;

no rows selected

SQL>

Upvotes: 0

Related Questions