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!

Concatenate date field with time field to create date-time field

Discussion in 'General' started by abspatel, Nov 20, 2014.

  1. abspatel

    abspatel Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I have a date field which is in the format 20140101 and a separate time field which is 8 numbers long, e.g. 8.30pm is 20305910.
    I want to concatenate the two fields to just give me one datetime field in the format yyyymmdd hh12:mi:ss
    Any help would be appreciated.
    So far I've tried using substring and then to_date however I keep getting various different errors.
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    As I know that to_date will remove the time and it just the date.

    If your column have date and time try like below.

    to_char(your_col,'YYYY-MM-DD HH24:MI:SS')

    select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Try with the below query.


    Code (SQL):


    SELECT to_timestamp('20140101 20305910','yyyymmdd HH24MISSFF') FROM dual

    -- or

    SELECT to_timestamp(20140101||' '||20305910,'yyyymmdd HH24MISSFF') FROM dual

     

    Regards
    Sambasiva Reddy.K
     
  4. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi,

    What are the data types of those 2 fields?

    Regards,
    Yowan Cristo
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Good question Yowan :)...

    I just hope that the "date field" and the "time field" are NOT of DATE datatypes (or TIMESTAMPS).

    I assume that they are character types (VARCHAR2).

    One way of doing it (if I may pursue your line of thought "abspatel") is:

    Code (SQL):
    WITH date_time AS
      ( SELECT '20140101' dt, '20305950' tm FROM dual
      )
    SELECT TO_CHAR(TO_DATE(dt
      ||' '
      || SUBSTR(tm, 1, 6) , 'YYYYMMDD HH24MISS'), 'YYYYMMDD HH12:MI:SS')
    FROM date_time;
    Note: In the portion "SELECT '20140101' dt, '20305950' tm FROM dual", you just have to replace with your table name and column names.

    P.S:
    1) What about the AM/PM portion ? You'll be losing this information if you keep the format as 'HH12:MI:SS'...Rather use 'HH24:MI:SS' or 'HH12:MI:SS AM'.
    2) What's is the significance of the last 2 digits of the time field ?
     
  6. abspatel

    abspatel Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Won't that make it into a string though? I need it to be a date field.
     
  7. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Yes definitely ... I assumed you were dealing with character fields all the way.

    So, if your target field is of DATE datatype, then just remove the TO_CHAR portion, i.e.,

    Code (SQL):
    WITH date_time AS
      ( SELECT '20140101' dt, '20305950' tm FROM dual
      )
    SELECT TO_DATE(dt
      ||' '
      || SUBSTR(tm, 1, 6) , 'YYYYMMDD HH24MISS')
    FROM date_time;
    Of course, if you do a SELECT from that field, you may not get the output in 'yyyymmdd hh12:mi:ss' format, but Oracle stores the date + time in the DATE datatype field. Just try:

    Code (SQL):
    SELECT TO_CHAR(your_date_field, 'yyyymmdd hh12:mi:ss') FROM your_table;
    and you'll see the correct output.