Home » SQL & PL/SQL » SQL & PL/SQL » Issue on Date Field (Oracle 10g)
Issue on Date Field [message #364668] Tue, 09 December 2008 08:28 Go to next message
kathiresan.c
Messages: 23
Registered: February 2008
Location: Tamil Nadu, India
Junior Member
Hi Friends,

When i executing following query it return zero row even though data is available.

1. SELECT ENAME FROM EMPLOYEE T
WHERE TO_DATE(T.COL1, 'dd/mm/yyyy') =
TO_DATE('09/12/2008', 'dd/mm/yyyy')

RESULT :
no records found


NOTE :
HERE COL1 is Date Field and stored as sysdate


Correct Query :
--------------

If i use additionally TRIM function in that query , it returns records.

2. SELECT ENAME FROM EMPLOYEE T
WHERE TRIM(TO_DATE(T.COL1, 'dd/mm/yyyy')) =
TO_DATE('09/12/2008', 'dd/mm/yyyy')

OR
3. SELECT ENAME FROM EMPLOYEE T
WHERE TO_DATE(T.COL1, 'dd/mm/yyyy') =
TO_DATE(SYSDATE, 'dd/mm/yyyy')

NOTE :
Both queries are working perfectly.


What is the problem in First Query ?
why didn't the query fetch the record?

if anyone know, please let me know

thanks advance
Re: Issue on Date Field [message #364670 is a reply to message #364668] Tue, 09 December 2008 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the type of COL1?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Quote:
TO_DATE(SYSDATE, 'dd/mm/yyyy')

Explain what is the purpose of TO_DATE and the meaning of TO_DATE on a date.

Regards
Michel
Re: Issue on Date Field [message #364674 is a reply to message #364670] Tue, 09 December 2008 08:46 Go to previous messageGo to next message
kathiresan.c
Messages: 23
Registered: February 2008
Location: Tamil Nadu, India
Junior Member
Thanks michel for reply ,

I list out the points :

1. COL1 Datatype is Date
2. I insert the values on that column as SYSDATE.
so the data stored like 12/9/2008 1:26:53 PM

so need to format a date
Re: Issue on Date Field [message #364675 is a reply to message #364674] Tue, 09 December 2008 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer my questions:

TO_DATE converts a string to a date
TO_DATE on date is meaningless and wrong.

Regards
Michel
Re: Issue on Date Field [message #364676 is a reply to message #364675] Tue, 09 December 2008 09:05 Go to previous messageGo to next message
kathiresan.c
Messages: 23
Registered: February 2008
Location: Tamil Nadu, India
Junior Member
Sorry michel...

can you please note it down the second point in previous post?

Here I explain clearly :

for example :

I inserted a record on that table at this day afternoon.

Simply i fetch record:

the corresponding record's date filed values is 12/9/2008 1:26:53 PM .

Here the format of date is different(included time and second)


Now i want to fetch the record based on date condition
for example
i want to see how many records are insertes on this day?

Can you please write query for this ?


Re: Issue on Date Field [message #364677 is a reply to message #364676] Tue, 09 December 2008 09:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
WHERE trunc(t.col1) = trunc(sysdate)
Re: Issue on Date Field [message #364678 is a reply to message #364668] Tue, 09 December 2008 09:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Have a look at (at least the end of) this thread: http://www.orafaq.com/forum/m/277319/96705/.
Re: Issue on Date Field [message #364679 is a reply to message #364668] Tue, 09 December 2008 09:12 Go to previous messageGo to next message
kathiresan.c
Messages: 23
Registered: February 2008
Location: Tamil Nadu, India
Junior Member
thank you michel ,

I just confused my self.

now i am clear with your reply...

thanks lot
Re: Issue on Date Field [message #364680 is a reply to message #364668] Tue, 09 December 2008 09:15 Go to previous message
kathiresan.c
Messages: 23
Registered: February 2008
Location: Tamil Nadu, India
Junior Member
thanks flyboy
Previous Topic: Shifting Data
Next Topic: Has anyone gotten dbms_ldap.open_ssl to work?
Goto Forum:
  


Current Time: Sat Dec 10 20:55:01 CST 2016

Total time taken to generate the page: 0.26355 seconds