DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to get the date?

  1. #1
    Join Date
    Sep 2001
    Posts
    99

    How to get the date?

    In sql server,I can do this
    "select * from table1 where year(ltime) = year(getDate()) and month(ltime) = month(getDate()) and day(ltime) = day(getDate())
    the ltime column is datetime type.

    but in oracle,I can do this
    "select * from table1 where year(ltime) = year(sysdate) and month(ltime) = month(sysdate) and day(ltime) = day(sysdate)
    but can not find year(),month,day() function ,How can I do?
    please help,thanks
    ocean

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    There are a couple of ways that you can do something similar, but in your case you would want ...

    select * from table1
    where TRUNC(ltime,'D') = TRUNC(sysdate,'D');

    (see the TRUNC function in the Oracle SQL Reference)

    In fact, since truncation defaults to 'Day' you could also ...

    select * from table1
    where TRUNC(ltime) = TRUNC(sysdate);

    ... although that is not quite so clear.

    One problem with this is that it could limit the use of indexes on the ltime column. Possible solutions would be to either create a function based index ...

    create index my_index on table1 (TRUNC(ltime));

    ... or turn your query into ...

    select * from table1
    where ltime >= TRUNC(sysdate) and ltime < Trunc(sysdate)+1;
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2001
    Posts
    99

    thank you very much

    thanks
    ocean

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width