1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Convert Oracle Timestamp to Date

Discussion in 'SQL PL/SQL' started by amy85, Apr 4, 2009.

  1. amy85

    amy85 Active Member

    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    80
    Hi guys just a simple issue, how does one convert Oracle Timestamp to Date?

    I have a table where I have a timestamp column. I need it in a date format since I am accessing the table through ASP code and somehow the the Microsoft OleDb provider is not supporting timestamp columns.

    I tried the following query

    Code (Text):

    SELECT TO_DATE (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS') AS my_date
      FROM DUAL
     
    But this is obviously not working, because I'm missing something. I also want to retain the time value in my date.

    Thanks
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    hi again :)

    no you can't use TO_DATE on a TIMESTAMP column, if you just want a date string you can do this
    Code (Text):

    SELECT TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS') AS my_date
      FROM DUAL
     
    But if you want your result to be of the date type, you can do this
    Code (Text):

    SELECT TO_DATE (TO_CHAR (SYSTIMESTAMP, 'YYYY-MON-DD HH24:MI:SS'),
                    'YYYY-MON-DD HH24:MI:SS'
                   ) AS my_date
      FROM DUAL
     
    Cheers again :)
     
    amy85 likes this.
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Plus, some simple ways of doing it.

    Code (SQL):

    SQL> SELECT SYSDATE DT FROM DUAL ;

    DT
    ---------
    08-APR-09

    SQL> SELECT SYSTIMESTAMP TS FROM DUAL;

    TS
    ---------------------------------------------------------------------------
    08-APR-09 09.50.08.118526 AM +05:30

    SQL> SELECT CAST(SYSTIMESTAMP AS DATE) DT FROM DUAL;

    DT
    ---------
    08-APR-09

    SQL> SELECT TRUNC(SYSTIMESTAMP) DT FROM DUAL;

    DT
    ---------
    08-APR-09

    SQL>
     
     
    amy85 likes this.
  4. LutzEbeling

    LutzEbeling Guest

    tyra and rajavu: thx!!!
     
  5. damolat

    damolat Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Code (SQL):
    Sql> SELECT trunc(systimestamp) DATE FROM dual;
     
  6. erpsindhu@gmail.com

    erpsindhu@gmail.com Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    75
    thanks for the answers