Filter by Date Connect In-DB?

[dan_b]

Hello All,

I'm trying to filter by date when building the Query in Connect In-DB. The SQL tests out ok; however, when I run the workflow, I get the following error message.  Does anyone know why I'm getting this error message and how to fix it?

Thanks!

Data Stream Out (2)        DataWrapOCI: Unable to prepare the query: "Select PPM_DWH.DWH_INV_INVESTMENT.INVESTMENT_ID, PPM_DWH.DWH_INV_INVESTMENT.INVESTMENT_NAME, PPM_DWH.DWH_INV_INVESTMENT.INVESTMENT_MANAGER, PPM_DWH.DWH_INV_INVESTMENT.GPM_MARKET_PRIMARY, PPM_DWH.DWH_INV_INVESTMENT.GPM_MRKT_ROLLOUT, PPM_DWH.DWH_INV_INVESTMENT.GPM_MARKET_PRIMARY_KEY, PPM_DWH.DWH_INV_INVESTMENT.GPM_FEASIBILITY, PPM_DWH.DWH_INV_INVESTMENT.GPM_ROOT_SKUS, PPM_DWH.DWH_INV_INVESTMENT.GPM_BRAND_MGR, PPM_DWH.DWH_INV_INVESTMENT.GPM_FIRST_MARKET, PPM_DWH.DWH_INV_INVESTMENT.GPM_VERIFICATION, PPM_DWH.DWH_INV_INVESTMENT.GPM_IMPLEMENTATION, PPM_DWH.DWH_INV_INVESTMENT.GPM_MRKT_REGION, PPM_DWH.DWH_INV_INVESTMENT.GPM_ROOT_SKU_ADD, PPM_DWH.DWH_INV_INVESTMENT.GPM_IDEATION From PPM_DWH.DWH_INV_INVESTMENT¶¶where PPM_DWH.DWH_INV_INVESTMENT.GPM_MRKT_ROLLOUT between '2016-01-01 00:00' AND '2017-04-25 00:00'" Error: ORA-01861: literal does not match format string¶¶

[LindaT]

Hi Dan,

You will want to modify your query to represent your date more like this

WHERE "DATE" BETWEEN TO_DATE('2007-12-30', 'yyyy-mm-dd') AND TO_DATE('2008-12-30', 'yyyy-mm-dd')

Here is a link to the TO_DATE Help

https://www.techonthenet.com/oracle/functions/to_date.php

[dan_b]

Hi Linda,

The Query works, but now I get a different error message. Do you know what that is? 

Browse In-DB (7) DataWrapOCI: Unable to prepare the query: "WITH "Tool1_b6b7" AS (Select PPM_DWH.DWH_INV_INVESTMENT.INVESTMENT_ID, PPM_DWH.DWH_INV_INVESTMENT.INVESTMENT_NAME, PPM_DWH.DWH_INV_INVESTMENT.INVESTMENT_MANAGER, PPM_DWH.DWH_INV_INVESTMENT.GPM_MARKET_PRIMARY, PPM_DWH.DWH_INV_INVESTMENT.GPM_MRKT_ROLLOUT, PPM_DWH.DWH_INV_INVESTMENT.GPM_MARKET_PRIMARY_KEY, PPM_DWH.DWH_INV_INVESTMENT.GPM_FEASIBILITY, PPM_DWH.DWH_INV_INVESTMENT.GPM_ROOT_SKUS, PPM_DWH.DWH_INV_INVESTMENT.GPM_BRAND_MGR, PPM_DWH.DWH_INV_INVESTMENT.GPM_FIRST_MARKET, PPM_DWH.DWH_INV_INVESTMENT.GPM_VERIFICATION, PPM_DWH.DWH_INV_INVESTMENT.GPM_IMPLEMENTATION, PPM_DWH.DWH_INV_INVESTMENT.GPM_MRKT_REGION, PPM_DWH.DWH_INV_INVESTMENT.GPM_ROOT_SKU_ADD, PPM_DWH.DWH_INV_INVESTMENT.GPM_IDEATION From PPM_DWH.DWH_INV_INVESTMENT where PPM_DWH.DWH_INV_INVESTMENT.GPM_MRKT_ROLLOUT between TO_DATE('2016-01-01 00:00','yyyy-mm-dd') AND TO_DATE('2017-04-25 00:00','yyyy-mm-dd')) SELECT * FROM "Tool1_b6b7" WHERE ROWNUM <= 500" Error: ORA-01830: date format picture ends before converting entire input string¶¶

[LindaT · Accepted solution]

Hi Dan,

You are missing your HH:MM  (Hour:Minute).  That is what I am assuming your 00:00 represents.  In your case, because you are doing a between, you could probably just remove the 00:00 from your date strings.

[dan_b]

Thank you Linda! Your solution worked!

[rashmi_joshi]

Hi Linda and others here,

I want to pull some data from an Oracle database but when I write the SQL query, it keeps giving me the errors 'literal does not match format string' or 'format picture ends before converting entire input string' or 'a non numeric character was found where a numeric was expected' . What am I missing?

Here are some more details:

I'm not using an IN-DB connection, but instead the Input tool with a custom query.

The format of the date column in Oracle is datetime (for example '01AUG2017:00:00:00')

For this exercise, the time does not matter

I would like to use a between function to limit the output.

Thanks in advance for your advice.

[rashmi_joshi]

Ok got it!

So you need to maintain the same format and order as in your Oracle database. As Linda said, you can exclude the hours, minutes and seconds if you don't need it.

I also had to use the to_date function even though this column was already a datetime one. The datetime tool in Alteryx though did not seem to work, maybe because I'm using a custom SQL query.

For mine the solution was:

Where fieldname Between to_date('01-09-2017', 'dd-mm-yyyy') And to_date('30-09-2017', 'dd-mm-yyyy')