CODEWITHSUNDEEP
sqlstringoracle-databasedateliterals
Jonas
Jonas

Reputation: 784

Cast Varchar2 to date in Oracle PL/SQL

I have a query in Oracle PL/SQL which gives an ordered list by the customer names, based on the % of bills, they paid with a delay. The query is the following

With date_check AS
    (Select *
        From tblBills
        Where Pay_Date Between CAST('2015-09-01' AS DATE) And CAST('2015-09-30' AS DATE)
    )
    Select ddo.CODE As "Customer Name",
      100.0 * ROUND(Cast(Count(fpp.ID) AS FLOAT)  / 
      ( Select Count(fpp2.ID)
        From date_check fpp2 
        Join tblMethods dppm2
         On fpp2.PAY_METHOD = dppm2.ID
        Where dppm2.IS_DELAYED = 'Y'), 2) As "% Delay"
    From date_check fpp
      Join tblMethods dppm On fpp.PAYMENT_METHOD_ID = dppm.ID
      Join tblCustomers ddo On fpp.CUSTOMER_ID = ddo.ID
    Where dppm.IS_DELAYED = 'Y'
    Group By ddo.CODE
    Order By Count(fpp.ID) Desc

When I run this query I receive the following exception:

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.

What is wrong?

Upvotes: 0

Views: 918

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

Either use date literals

Where Pay_Date Between date '2015-09-01' And date '2015-09-30'

or use the to_date function with an explicit format mask to convert strings to dates

Where Pay_Date Between to_date('2015-09-01', 'YYYY-MM-DD') And 
                       to_date('2015-09-30', 'YYYY-MM-DD')

A straight cast will use whatever the current session's nls_date_format is set to. That is unlikely to be the same for every person over time so it is not something that you want to build code that relies upon.

Upvotes: 4

Related Questions