Member Avatar for saideepak89

hi i have the table in which date valve is like this 11/11/2009 6:08:29 AM how can i compare the value or select them , the to_date is working in SELECT but,i want to select the records using this colmn and by giving value
ie:

SELECT * FROM SENTMAIL WHERE SENTMAIL_SENT_DATE='01-OCT-2011'

this statement is retrieving nothing.

thanks in advance

  • 3 Contributors
  • 4 Replies
  • 103 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

Quick checklist and a fast fix you can apply.

First, confirm the column type — if it is DATE you must compare it to DATE values, not to plain strings; if it is a text column you should convert it to DATE for reliable querying. Use the database dictionary to check the type (USER_TAB_COLUMNS). The sample timestamp string you posted parses with this format mask:

TO_DATE('11/11/2009 6:08:29 AM', 'MM/DD/YYYY HH12:MI:SS AM')

About the slow results: and were right that applying functions to the date column will return correct rows, but wrapping the column in functions prevents Oracle from using a normal index and forces a full scan. For fast, index-usable queries test a range predicate instead of transforming the column. For a single calendar day prefer a half-open range so the column is still sargable:

WHERE SENTMAIL_SENT_DATE >= DATE '2011-10-01'
  AND SENTMAIL_SENT_DATE <  DATE '2011-10-02'

If your column is a VARCHAR2 holding values like the sample, convert the data and queries to use a DATE column. A safe workflow:

ALTER TABLE SENTMAIL ADD (sent_date_dt DATE);

UPDATE SENTMAIL
SET sent_date_dt = TO_DATE(SENTMAIL_SENT_DATE, 'MM/DD/YYYY HH12:MI:SS AM');

-- verify results, then drop/rename as needed

If you must keep using a function-based filter (for example truncation by day in many queries), create a function-based index or a virtual/generated column on that expression so the optimizer can use an index:

CREATE INDEX idx_sentdate_trunc ON SENTMAIL (TRUNC(SENTMAIL_SENT_DATE));

Finally, use EXPLAIN PLAN / DBMS_XPLAN.DISPLAY to confirm index use, gather statistics after changes, and prefer bind variables for repeated searches. These steps will make the lookups correct and perform well for large tables.

Recommended Answers

All 4 Replies

Member Avatar for debasisdas

try this.

SELECT * FROM SENTMAIL WHERE trunc(SENTMAIL_SENT_DATE)='01-OCT-2011'
Member Avatar for ruhi

You can also use

SELECT * FROM SENTMAIL WHERE to_char(SENTMAIL_SENT_DATE, 'fmDD-MON-YYYY')='01-OCT-2011'
Member Avatar for saideepak89

thanks the query is working ,but it is taking so much time

Member Avatar for debasisdas

What is the volume of data ?

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.