Home » SQL & PL/SQL » SQL & PL/SQL » Displaying Current Date records (ORACLE 10G, RHEL EE 5)
icon1.gif  Displaying Current Date records [message #420111] Thu, 27 August 2009 01:57 Go to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

I tried displaying few records based on my system date but was unable to display records.
Below is the sample scenario i tried.

SQL> select sysdate from dual;

SYSDATE
---------
27-AUG-09

SQL> desc emp
Name Null? Type
----------------------- -------
ENUM NUMBER
NAME VARCHAR2(10)
CREATION DATE

SQL> select * from emp;

ENUM NAME CREATION
---------- ---------- ---------
11 aaaa 22-AUG-09
22 bbbb 24-AUG-09
33 CCCC 27-AUG-09
44 DDDD 27-AUG-09

SQL> select * from emp where creation=sysdate;

no rows selected

SQL>

How can we go about displaying the present date records using system date parameter,,!!
Please update the same.

Thanks in Adv,
Sanjay.S


[Updated on: Thu, 27 August 2009 02:02]

Report message to a moderator

Re: Displaying Current Date records [message #420116 is a reply to message #420111] Thu, 27 August 2009 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SYSDATE also contains a time part (even if your default format does not display it).

Have a look at TRUNC function.

Regards
Michel

Re: Displaying Current Date records [message #420121 is a reply to message #420111] Thu, 27 August 2009 02:50 Go to previous messageGo to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi Michel,

Thanks for the update. I got the required result.
Below is the query i tried and got the output.

SQL> select * from emp where creation=TO_DATE(sysdate,'DD-MON-YY');

ENUM NAME CREATION
---------- ---------- --------------------
22 BBBB 27-AUG-09
33 GGGG 27-AUG-09

Thanks,
Sanjay.

[Updated on: Thu, 27 August 2009 02:51]

Report message to a moderator

Re: Displaying Current Date records [message #420130 is a reply to message #420121] Thu, 27 August 2009 03:17 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
to_date function on a date and then using YY as format mask. Shocked

XE@SQL>  select x,
  2     to_date(x,'dd-Mon-yy') x1,
  3     to_char(to_date(x,'dd-Mon-yy'),'dd-mon-yyyy') x2,
  4          to_char(x,'dd-Mon-yyyy') x3
  5  from
  6   (
  7   select to_date('10-Feb-1909','dd-Mon-yyyy') x from dual
  8   )
  9  /

X         X1        X2                   X3
--------- --------- -------------------- -----------
10-feb-09 10-feb-09 10-feb-2009          10-Feb-1909

1 row selected.

XE@SQL>


Please stop doing that use trunc function on sysdate as Michel suggested

[Updated on: Thu, 27 August 2009 03:20]

Report message to a moderator

Re: Displaying Current Date records [message #420157 is a reply to message #420121] Thu, 27 August 2009 05:50 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To emphasize what bonker said, TO_DATE works on a string, if you give it a date there is an implicit conversion that may or may not work depending on you environment.
See:
SQL> select * from emp where hiredate=TO_DATE(sysdate,'DD-MON-YY');
select * from emp where hiredate=TO_DATE(sysdate,'DD-MON-YY')
                                         *
ERROR at line 1:
ORA-01843: not a valid month

Regards
Michel
Previous Topic: OSCommand without Java
Next Topic: Convert no rows returned to zero
Goto Forum:
  


Current Time: Thu Dec 08 10:40:46 CST 2016

Total time taken to generate the page: 0.08939 seconds