Home » SQL & PL/SQL » SQL & PL/SQL » Set timestamp with milliseconds to default to 0 milliseconds (Oracle 11g)
Set timestamp with milliseconds to default to 0 milliseconds [message #667396] Tue, 26 December 2017 16:55 Go to next message
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.
Re: Set timestamp with milliseconds to default to 0 milliseconds [message #667397 is a reply to message #667396] Tue, 26 December 2017 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 25796
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.

Re: Set timestamp with milliseconds to default to 0 milliseconds [message #667402 is a reply to message #667397] Wed, 27 December 2017 00:44 Go to previous message
Littlefoot
Messages: 21331
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:
  


Current Time: Fri Jan 19 18:28:26 CST 2018

Total time taken to generate the page: 0.01632 seconds