Member Avatar for mimsc

Here's my code...basically, I hard coded yesterday just to make sure it works...Im trying to get it to display results everyday

String sqlStmt = "SELECT distinct count (lc.con_id) as TotalLeads FROM LEADS L inner join leads_contactinfo lc on l.fk_conid = lc.con_id" +
                        " WHERE l.FK_CONID = lc.CON_ID AND lc.FK_AGENTID = ? AND CON_INITDATE > to_date('2007/07/13', 'yyyy/mm/dd') AND CON_INITDATE < to_date('2007/08/14', 'yyyy/mm/dd')";

any suggestions would be greatly appreciated

  • 2 Contributors
  • 4 Replies
  • 86 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by mimsc

Dani AI

Generated

was on the right track: use Oracle date arithmetic rather than trying to stuff the text "sysdate-30" into TO_DATE. The ORA-01841 came from treating a literal string as a date (TO_DATE expects a parsable date string), so Oracle tried to parse something invalid and failed.

Use SYSDATE directly and compute boundaries on the right-hand side so the column stays usable by indexes. Also count unique lead IDs with COUNT(DISTINCT ...) rather than SELECT DISTINCT COUNT(...). A simple, safe pattern that includes all of today is:

SELECT COUNT(DISTINCT lc.con_id) AS TotalLeads
FROM leads l
JOIN leads_contactinfo lc
  ON l.fk_conid = lc.con_id
WHERE lc.fk_agentid = :agentid
  AND lc.con_initdate >= TRUNC(SYSDATE) - 30
  AND lc.con_initdate <  TRUNC(SYSDATE) + 1;

Notes and quick checks for :

  • The join condition already enforces l.fk_conid = lc.con_id, so you do not need to repeat it in WHERE.
  • TRUNC(SYSDATE) - 30 gives midnight 30 days ago; using < TRUNC(SYSDATE) + 1 includes all rows for today. If you want the last 30 complete days excluding today, use < TRUNC(SYSDATE).
  • Verify the column type (DATE vs TIMESTAMP) if times are important. Do not wrap the table column in TRUNC() in the WHERE clause if you want Oracle to use an index on that column.
  • In JDBC keep the ? bind for agent id and use a PreparedStatement to set it; avoid concatenating date literals into SQL.

These changes will remove the TO_DATE parsing error and return the rolling 30-day counts reliably.

Recommended Answers

All 4 Replies

Member Avatar for debasisdas

instead of hardcoding the dates use sysdate and sysdate-30 for the desired output.

Member Avatar for mimsc

thanx...let me try that

Member Avatar for mimsc

ok...I tried that...im not too savvy with sql....should it look like this?:

String sqlStmt = "SELECT distinct count (lc.con_id) as TotalLeads FROM LEADS L inner join leads_contactinfo lc on l.fk_conid = lc.con_id" +
" WHERE l.FK_CONID = lc.CON_ID AND lc.FK_AGENTID = ? AND CON_INITDATE > to_date('sysdate-30', 'yyyy/mm/dd') AND CON_INITDATE < to_date('sysdate', 'yyyy/mm/dd')";

Member Avatar for mimsc

im getting this error now java.sql.SQLException: ORA-01841: (full) year must be between -4713 and +9999, and not be

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.