CODEWITHSUNDEEP
plsql
Dan13
Dan13

Reputation: 17

Removing the millisecond from the timestamp

I need to convert timestamp to date in PL SQL. I get Input as '2016-08-01T09:16:47.000' from webservice, I require '8/01/2016 9:16:47 AM'. I take the input as VARCHAR2.

It can be "AM" or "PM".

I tried using select TRUNC(to_timestamp('2016-08-01T09:16:47.000','YYYY-MM-DD"T"HH24:MI:SS.ff3')) from dual; but the time part is removed.

Upvotes: 0

Views: 3009

Answers (3)

JDro04
JDro04

Reputation: 670

select to_char(to_timestamp('08/01/2016 09:16:47.000000000 AM', 'MM/DD/YYYY HH:MI:SS.FF AM'),'MM/DD/YYYY HH:MI:SS AM') from dual;

Edit: This works for am or pm

select to_char(to_timestamp('08/01/2016 09:16:47.000000000 PM', 'MM/DD/YYYY HH:MI:SS.FF AM'),'MM/DD/YYYY HH:MI:SS AM') from dual;

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132670

Your query:

select TRUNC(to_timestamp('2016-08-01T09:16:47.000','YYYY-MM-DD"T"HH24:MI:SS.ff3'))
  from dual;

returns a date value. How you display that is then a matter of what format mask you use to convert it to a string. Without a format mask the default NLS setting will be used e.g. I get:

SQL> select TRUNC(to_timestamp('2016-08-01T09:16:47.000','YYYY-MM-DD"T"HH24:MI:SS.ff3'))
     from dual;

TRUNC(TO_
---------
01-AUG-16

becaiuse my NLS_DATE_FORMAT setting is currently 'DD-MON-RR'. Using an explicit format mask:

  1  select TO_CHAR( TRUNC(to_timestamp('2016-08-01T09:16:47.000','YYYY-MM-DD"T"HH24:MI:SS.ff3')
  2                  'YYYY-MM-DD HH:MI:SS AM')
  3*   from dual;

TO_CHAR(TRUNC(TO_TIMES
----------------------
2016-08-01 12:00:00 AM

That shows that using trunc was wrong as it removed the time altogether. So:

  1  select TO_CHAR( to_timestamp('2016-08-01T09:16:47.000','YYYY-MM-DD"T"HH24:MI:SS.ff3'),
  2                  'YYYY-MM-DD HH:MI:SS AM')
  3*   from dual;

TO_CHAR(TO_TIMESTAMP('
----------------------
2016-08-01 09:16:47 AM

Note this works for PM times also:

  1  select TO_CHAR( to_timestamp('2016-08-01T13:16:47.000','YYYY-MM-DD"T"HH24:MI:SS.ff3'),
  2                  'YYYY-MM-DD HH:MI:SS AM')
  3*   from dual;

TO_CHAR(TO_TIMESTAMP('
----------------------
2016-08-01 01:16:47 PM

Upvotes: 1

Yaron Idan
Yaron Idan

Reputation: 6765

You should use to_char instead of trunc. In your example, the correct operation would be - to_char('08/01/2016 09:16:47.000000000 AM','dd:mm:yyyy hh:mi:ss am')

Upvotes: 0

Related Questions