Get the date time from Oracle date time stamp?????

[Ric]

I am using crystal reports to attempt to extract information from this
database but I need to know which records were last updated. I have
managed to find a field in Oracle and its value is based on a trigger
function as shown below

begin

select TIMESTAMP_SEQ.nextval into :new.NURS_TIMESTAMP from dual;

end;

This at 9.45am (UK) on 05/09/2003 generated a value of 5,048,868

My main expertise is in SQL Server, my question is can I reverse
engineer this value to calculate the time? Or is it like an identity
field in SQL server (i.e. An automatic incremental counter)

I would really appreciate any help or pointers to some good web
sources.

[Frank]

Ric wrote:

I am using crystal reports to attempt to extract information from this
database but I need to know which records were last updated. I have
managed to find a field in Oracle and its value is based on a trigger
function as shown below

begin

select TIMESTAMP_SEQ.nextval into :new.NURS_TIMESTAMP from dual;

end;

This at 9.45am (UK) on 05/09/2003 generated a value of 5,048,868

My main expertise is in SQL Server, my question is can I reverse
engineer this value to calculate the time? Or is it like an identity
field in SQL server (i.e. An automatic incremental counter)

I would really appreciate any help or pointers to some good web
sources.

Oracle dates are always WITH time!
The obvious internal pseudo column would be sysdate:

:new.NURS_TIMESTAMP := sysdate
--
Regards, Frank van Bortel

[Walt]

Ric wrote:

I am using crystal reports to attempt to extract information from this
database but I need to know which records were last updated. I have
managed to find a field in Oracle and its value is based on a trigger
function as shown below

begin

select TIMESTAMP_SEQ.nextval into :new.NURS_TIMESTAMP from dual;

end;

This at 9.45am (UK) on 05/09/2003 generated a value of 5,048,868

My main expertise is in SQL Server, my question is can I reverse
engineer this value to calculate the time? Or is it like an identity
field in SQL server (i.e. An automatic incremental counter)

Although this field is named Timestamp_seq, it's not really a timestamp,
it's just a sequence. Everytime you call TIMESTAMP_SEQ.nextval it gives
you the previous value plus one (look up Sequence in your docs). I
don't know how you would reverse engineer it to come up with a date/time
of the last update, but you could use it to find the most recently
updated record

Select * from mytable where NURS_TIMESTAMP =
(Select max(NURS_TIMESTAMP) from mytable)

A little more work and you can return the last n updated records.

HTH.
--
//-Walt
//
//

[Guido Konsolke]

Hi Walt,

sorry, little correction inside.

"Walt" <wa**@boatnerd.com.invalid> wrote
news:3F***************@boatnerd.com.invalid...

Although this field is named Timestamp_seq, it's not really a timestamp, it's just a sequence. Everytime you call TIMESTAMP_SEQ.nextval it gives you the previous value plus one (look up Sequence in your docs). I
If you also read this, you will see that there is
no guarantee for 'previous value plus 1'.
For many reasons...
don't know how you would reverse engineer it to come up with a date/time of the last update, but you could use it to find the most recently
updated record

Select * from mytable where NURS_TIMESTAMP =
(Select max(NURS_TIMESTAMP) from mytable)

A little more work and you can return the last n updated records.

HTH.
--
//-Walt
//
//

Greetings,
Guido