how to get milliseconds

[ndedhia1]

I want hr:min:sec:mill isecs.
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:

[code=oracle]

SQL> ed
Wrote file afiedt.buf

1 select
2 to_char(systime stamp,'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(systime stamp,'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.728330 000 AM

SQL>

[/code]

[madankarmukta]

I want hr:min:sec:mill isecs.
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

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:sec ond:millisecond s

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