OraFAQ Forum: SQL & PL/SQL » Set timestamp with milliseconds to default to 0 milliseconds

Home » SQL & PL/SQL » SQL & PL/SQL » Set timestamp with milliseconds to default to 0 milliseconds (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Set timestamp with milliseconds to default to 0 milliseconds [message #667396]

Tue, 26 December 2017 16:55

pallaviprakash
Messages: 2
Registered: February 2015

Junior Member

Hi all,
I have a timestamp column in Oracle that has format 'MM/DD/YYYY HH24:MI.SxFF6'. The data looks like below:

11/09/1917 10:45:28.230000
10/19/2014 18:09:28.410000
12/19/2011 11:06:28.340000
I need the timestamp to retain the value except for getting the milliseconds which need to be defaulted to 000000. I tried query -

cast(to_char(Local_time, 'MM/DD/YYYY HH24:MI:SS') as timestamp(6))
But it is throwing error - "Not valid month"

Does anyone have any ideas on what I can try to get milliseconds to 0. I use Toad to query the table.

Report message to a moderator

Re: Set timestamp with milliseconds to default to 0 milliseconds [message #667397 is a reply to message #667396]

Tue, 26 December 2017 18:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Neither DATE nor TIMESTAMP have any format.
Both are stored as internal binary values that are NOT directly human readable.

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

SQL> DECLARE
2 TS TIMESTAMP := TO_TIMESTAMP('2017-12-25 12:34:56.7890','YYYY-MM-DD-HH24:MI:SS.FF');
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(TO_CHAR(TS,'YYYY-MM-DD-HH24:MI:SS')||'.000000');
5 END;
6 /
2017-12-25-12:34:56.000000

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Set timestamp with milliseconds to default to 0 milliseconds [message #667402 is a reply to message #667397]

Wed, 27 December 2017 00:44

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How about CAST AS TIMESTAMP(0)?

SQL> with test
  2       as (select to_timestamp ('11/09/1917 10:45:28.230000',
  3                                'mm/dd/yyyy hh24:mi:ss.ff')
  4                     col
  5             from dual)
  6  select col,
  7         cast (col as timestamp (0)) res_1,   --> "truncate" timestamp to seconds
  8         to_char (cast (col as timestamp (0)), 'mm/dd/yyyy hh24:mi:ss.ff6')  --> apply TO_CHAR to display it properly
  9            res_2
 10    from test;

COL                         RES_1                       RES_2
--------------------------- --------------------------- ---------------------------
09.11.17 10:45:28,230000000 09.11.17 10:45:28           11/09/1917 10:45:28.000000

SQL>

P.S. BS, please read and follow the forum guidelines, How to use {code} tags and make your code easier to read

/forum/fa/5767/0/

[Updated on: Wed, 27 December 2017 00:47]

Report message to a moderator

Previous Topic:	Create a combination DML Trigger
Next Topic:	Open cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 25 03:05:31 CDT 2025