How to fetch yesterday records for an array based date column using aep query service | Community
Skip to main content
R
RitwikMo
Level 2
December 20, 2024
Solved

How to fetch yesterday records for an array based date column using aep query service

  • December 20, 2024
  • 1 reply
  • 8511 views

I have an array based date column called Billinvoicedate which is of date type.I want to calculate and fetch all the yesterday records for that particular date column.But i am constantly getting error while trying different sort of sql queries.

 

e.g. select * from pe_cust_ren_sub where DATE(_pe.rensub.BillinvoiceDate ) =DATE(CURRENT_DATE -1 )

 

I am getting error as  follows:

1:43:06 PM > ErrorCode: 08P01 queryId: cf72f42b-687e-4b26-a821-af1d7b20f241 Unknown error encountered. Reason: [cannot resolve 'CAST(pe_cust_ren_sub ._pe.`rensub`.`BillInvoiceDate` AS DATE)' due to data type mismatch: cannot cast array<date> to date; line 4 pos 7; 

    Best answer by DavidRoss91

    Hi @ritwikmo 

    You'd have to explode the array. Try below:

    SELECT *

    FROM pe_cust_ren_sub

    WHERE EXISTS (

    SELECT 1

    FROM explode (BillInvoiceDate) AS date

    WHERE date = CURRENT_DATE - INTERVAL '1 day'

    1 reply

    DavidRoss91
    Community Advisor
    DavidRoss91Community AdvisorAccepted solution
    Community Advisor
    December 20, 2024

    Hi @ritwikmo 

    You'd have to explode the array. Try below:

    SELECT *

    FROM pe_cust_ren_sub

    WHERE EXISTS (

    SELECT 1

    FROM explode (BillInvoiceDate) AS date

    WHERE date = CURRENT_DATE - INTERVAL '1 day'

      Terms & ConditionsAccessibility statement

      Loading footer...