how to get milliseconds

[ndedhia1]

I want hr:min:sec:millisecs.
Can someone please tell me DATETIME is the correct type and what should go inside the parantheses.

If i am doing a:
CREATE TABLE startTable
(TIMESTAMP DATETIME()

thanks

[amitpatel66]

Firstly, you cannot use the column name as timestamp because its an oracle keyword.

In detail about timestamp, Check here

[ndedhia1]

I will change the column name to something else but I still was not able to figure out the code that gave me hour, min, sec and milliseconds.

thanks

[amitpatel66]

Try this:

 
SQL> ed
Wrote file afiedt.buf
 
  1  select
  2     to_char(systimestamp,'dd-mm-yyyy hh24:mi:ss.FF') as ts,
  3     to_timestamp(
  4        to_char(sysdate,'dd-mm-yyyy hh24:mi:ss.')
  5        ||
  6        to_char(
  7           to_number(
  8              to_char(systimestamp,'FF')
  9           ) + 250),'dd-mm-yyyy hh24:mi:ss.FF'
 10     ) as ts_milliseconds
 11* from dual
SQL> /
 
TS                            TS_MILLISECONDS
----------------------------- ----------------------------------------------------------------------
26-02-2009 06:51:09.072583    26-FEB-09 06.51.09.728330000 AM
 
SQL> 
 
 

[madankarmukta]

@ndedhia1

Hi,

I would suggest you to specify the format in which you want to display the data from the table column while creating the table itself.

Thanks!

[ndedhia1]

I want the display to be:
hour:minute:second:milliseconds

so is this the write syntax?
CREATE TABLE startTable(
ERROR_TIME DATETIME('HH:MM:SS:nnn'));

thanks

[amitpatel66]

You dont have anything called datetime() in oracle. I think it is in MS SQL server. if you are working with MS SQL then you thread needs to be moved to that forum. Please confirm which database you are using?

If you are using oracle, then getting Millisecond as I suggested in my previous post.You can use that query.

[ndedhia1]

sorry..i have moved the thread over to mysql

thanks

[amitpatel66]

Thread moved from oracle forum to mysql forum since the question is related to MYSQL DB

Moderator