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 timestamp to date time

Discussion in 'SQL PL/SQL' started by sahoo, Jul 18, 2017.

Tags:
  1. sahoo

    sahoo Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Georgia
    Hi All,

    Need small info, any idea how to derive date and time from timestamp which column.
    date what i get from upstream will be like this : 2017-07-17T07:23:37.265Z and i store this in varchar column and trying to insert only date and time should be like : 2017-07-17 07:23:37, any help would be helpful.

    Thanks,
    Sahoo.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    146
    Trophy Points:
    830
    Location:
    Russian Federation

    Hi.
    Why the value is stored as a string?

    Helpfull : to_char,to_date

    Code (Text):

    SELECT cast(to_TIMESTAMP ('2017-07-17 07:23:37.265','yyyy-mm-dd HH24:mi:ss.ff3') AS DATE) FROM dual;
     
     
    Last edited: Jul 18, 2017
  3. sahoo

    sahoo Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Georgia
    Hi,
    Reason, i am using varchar2 to store is, because i tried inserting into timestamp column, but it error'ed out, so thought i can ask java resource, who inserts date into oracle table, insert as string, and later i can use like below
    create table myTable_t2 ( served_time varchar2(100) );
    insert into myTable_t2( served_time ) values ( '2017-06-16T18:45:38.571Z');
    select TO_CHAR(TO_TIMESTAMP (served_time, 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'),'DD-MON-RRRR HH24:MI:SS') dt FROM myTable_t2;

    let me know, if you have any suggestions for me.

    Thanks,
    Sahoo.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,601
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The 'suggestion' is you use a TIMESTAMP column for such data; using VARCHAR2 causes problems such as Oracle doesn't know the data is date/time or timestamp and won't sort or process it properly. Read here:

    https://dfitzjarrell.wordpress.com/2016/11/17/that-still-aint-right/

    There is absolutely no reason you cannot adjust that string to meet the requirements for storing as a timestamp, any basic text editor provides search and replace functionality to replace the T with a space and remove the Z characters from these strings. At that point it's a simple conversion:

    Code (SQL):
    BING @ quanghoo > SELECT to_timestamp('2017-06-16 18:45:38.571','RRRR-MM-DD HH24:MI:SS.FF') FROM dual;

    TO_TIMESTAMP('2017-06-1618:45:38.571','RRRR-MM-DDHH24:MI:SS.FF')
    ---------------------------------------------------------------------------
    16-JUN-17 06.45.38.571000000 PM

    BING @ quanghoo >
     
    You can't expect Oracle to do all of your work.