1

I have the following TIMESTAMP value in oracle database.

26-JUL-12 11.01.40.000000000 AM

When i getting this value from table , it displays without millisecond value like,

26-JUL-12 11.01.40 AM

But, i want to get this value like this format,

26-JUL-12 11.01.40.000 AM

Is there any possible to get timestamp value in this format?

Please help me.

Thanks in advance..

Babu R
  • 1,025
  • 8
  • 20
  • 40
  • http://stackoverflow.com/questions/2343708/oracle-fetch-date-time-in-milliseconds-from-date-datatype-field... – chinna_82 Aug 09 '12 at 06:42
  • @chinna_82, that's not a duplicate. It is about the `differences` between the date and timestamp data-types. The OP already has a timestamp just doesn't know how to select it from Java. – Ben Aug 09 '12 at 07:48

2 Answers2

3
select to_char(systimestamp,'dd-mon-rrrr hh:mi:ss:ff3am') from dual;
Ilya
  • 29,135
  • 19
  • 110
  • 158
Raj
  • 31
  • 2
1

This article states:

An Oracle DATE stores the date and time to the second. An Oracle TIMESTAMP stores the date and time to up to 9 digits of subsecond precision, depending on the available hardware.

Both are implemented by storing the various components of the date and the time in a packed binary format. From the Oracle Concepts Guide section on dates

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

You can use the DUMP() function to see the internal representation of any particular date (or any other value for that matter), but that's probably more than you need (or want) to know.

Community
  • 1
  • 1
chinna_82
  • 6,353
  • 17
  • 79
  • 134
  • Sorry i'm not getting clear idea. can you post any example with dump() regarding get timestamp value? – Babu R Aug 09 '12 at 06:52
  • check this http://www.techonthenet.com/oracle/functions/dump.php and http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions048.htm – chinna_82 Aug 09 '12 at 07:16