471,092 Members | 1,481 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

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.
Jul 19 '05 #1
3 25745
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

Jul 19 '05 #2
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
//
//
Jul 19 '05 #3
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
Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Don | last post: by
6 posts views Thread by Mike Charney | last post: by
5 posts views Thread by Des | last post: by
1 post views Thread by Susan Bricker | last post: by
16 posts views Thread by W. eWatson | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.