CODEWITHSUNDEEP
oracle-databaseoracle11gtimestamp
David Garcia
David Garcia

Reputation: 2696

Oracle: TIMESTAMP data type

i am using timestamp for a field to store date and time, however when i fetch the rows from the table i get loads of zeros in the timestamp field.

SQL> select * from booking_session;

     BK_ID|BK_DATE
----------|-------------------------
         1|18-MAR-12 10.00.00.000000
         2|18-MAR-12 10.25.00.000000
         3|18-MAR-12 10.30.00.000000
         4|18-MAR-12 10.35.00.000000
         5|18-MAR-12 10.40.00.000000

The following is the insert statement

INSERT INTO BOOKING_SESSION VALUES (1,TO_TIMESTAMP('18/03/2012 10:00', 'DD/MM/YYYY HH24:MI')

Can someone please tell me how can the bk_date format be stored like 18-MAR-12 10.00 or at least have AM or PM next to it.

Thanks in advance.

Upvotes: 1

Views: 6397

Answers (2)

Abraham Bruck
Abraham Bruck

Reputation: 49

Try the following query:

select * from booking_session where trunc(bk_date) = to_date('18-03-2012', 'dd-mm-yyyy');

Upvotes: 1

Nick Krasnov
Nick Krasnov

Reputation: 27251

You can declare BK_DATE column as timestamp(precision) where precision is a number of decimal digits to store fraction of a second. So you might have declared BK_DATE timestamp(0). If you do not need track time down to a fraction of a second use date data type to store dates. In any case time part (hh:mi:ss) will be there and if you insert data as you did, specifying only hour and minutes, then seconds will be fill in with zeros. You can use to_char function and appropriate date format mask to get rid of those trailing zeros at display time:

-- sample of data
SQL> with t1(BK_ID, BK_DATE) as(
  2    select 1, to_timestamp('18-MAR-12 10.00.00.000000', 'DD-MON-RR HH:MI:SSxFF6') from dual union all
  3    select 2, to_timestamp('18-MAR-12 10.25.00.000000', 'DD-MON-RR HH:MI:SSxFF6') from dual union all
  4    select 3, to_timestamp('18-MAR-12 10.30.00.000000', 'DD-MON-RR HH:MI:SSxFF6') from dual union all
  5    select 4, to_timestamp('18-MAR-12 10.35.00.000000', 'DD-MON-RR HH:MI:SSxFF6') from dual union all
  6    select 5, to_timestamp('18-MAR-12 10.40.00.000000', 'DD-MON-RR HH:MI:SSxFF6') from dual
  7  )
  8  select bk_id
  9       , to_char(bk_date, 'DD-MON-RR HH:MI') bk_date
 10    from t1
 11  ;


     BK_ID BK_DATE
---------- ------------------------
         1 18-MAR-12 10:00
         2 18-MAR-12 10:25
         3 18-MAR-12 10:30
         4 18-MAR-12 10:35
         5 18-MAR-12 10:40

Upvotes: 4

Related Questions