0

I'am translating MySQL query to Oracle/SQL and since I don't have enought experience in Oracle/SQL this kind of error is unclean for me.

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.

And my work is here

   SELECT * FROM 
                (SELECT p.ProjectID, p.CustomName, p.Name
                        FROM projects  p
                        INNER JOIN 
                            users u
                        ON
                            u.UserID =  193
                        WHERE 
                            u.User_roleID = 1
                    UNION
                    SELECT p.ProjectID, p.CustomName, p.Name 
                        FROM projects  p
                        WHERE 
                            (p.Responsible_person_id = 193 OR p.Delivery_contact = 193) 
                        AND 
                            (SYSDATE BETWEEN to_date(p.StartDate) AND to_date(p.EndDate))
                        AND 
                            p.status = 2
                    UNION
                    SELECT rs.ProjectID, pr.CustomName, pr.Name 
                        FROM 
                            responsible_persons  rs
                        LEFT JOIN 
                            projects  pr 
                        ON 
                            pr.ProjectID = rs.ProjectID
                        WHERE 
                            rs.UserID = 193
                        AND 
                            (SYSDATE BETWEEN to_date(pr.StartDate) AND to_date(pr.EndDate))
                        AND 
                            pr.status = 2
                    UNION
                    SELECT p.ProjectID, p.CustomName, p.Name 
                        FROM project_users_schedule_dates pusd
                        LEFT JOIN projects p 
                        ON
                            p.ProjectID = pusd.ProjectID
                        WHERE pusd.UserID = 193
                        AND
                           (SYSDATE BETWEEN to_date(pusd.StartDate) AND to_date(pusd.EndDate+1))                    
                        AND p.status = 2) a
                --GROUP BY a.ProjectID
                ORDER BY a.CustomName, a.ProjectID

So far I check line-by-line and I can not see what is wrong here.

What I miss here ? Where the error comes from ?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • 4
    Did you do an research on the error? It's pretty common. You have `to_date()` calls without a format mask specified, so it's using your session NLS_DATE_FORMAT. But before you change those, what data types are all the StartDate and EndDate columns? The `pusd.EndDate+1` suggests that is already a date (as it should be). – Alex Poole Aug 31 '20 at 13:34
  • Yes, as I meantion in post above I did reserch and couldn't find any common mistake. StartDate and EndDate are varchar data type –  Aug 31 '20 at 13:41
  • 1
    OK, that's bad practice, but if that is what you are stuck with then what values are in those strings? You need to specify the appropriate format. ([See the docs](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_DATE.html).) The `+1` doesn't make sense for a string value that represents a date, though. You might mean `to_date(psud.EndDate, '') + 1` ? – Alex Poole Aug 31 '20 at 13:43
  • I need to add INTERVAL 1 DAY to EndDate –  Aug 31 '20 at 13:45
  • 1
    If you need to add interval to a date, then you have to add it to the DATE data type that results from you use of to_date. if psud.EndDate is actually a varchar, then they are just character strings that are recognized as a "date" only by a human looking at it, not by the database. As far as the database is concerned '05-Aug-2020' functionally no different than 'here is your sign'. And as said you need to know the format of that character string, and describe it to to_date. All in all, storing a date as a varchar is a huge mistake and should be corrected just like any other bug. – EdStevens Aug 31 '20 at 14:16
  • 1
    Continued ... as regard the format of the character string representation, what date is represented by '05/04/12'? What is the month, the day, and the year. I'll give you 6 guesses and guarantee the first 5 will be wrong. I've NEVER seen a place where date was stored as a varchar but what there weren't inconsistencies in the format used. The proper format description for one row will be incorrect for another. – EdStevens Aug 31 '20 at 14:19

0 Answers0