f



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

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.
0
Richard
9/5/2003 9:29:29 AM
comp.databases.oracle 1791 articles. 0 followers. tgugger5 (31) is leader. Post Follow

3 Replies
709 Views

Similar Articles

[PageSpeed] 52

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

0
Frank
9/5/2003 12:08:22 PM
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
// 
//
0
Walt
9/5/2003 4:25:50 PM
Hi Walt,

sorry, little correction inside.

"Walt" <walt@boatnerd.com.invalid> wrote
news:3F58B90E.A71867CB@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


0
Guido
9/8/2003 11:34:07 AM
Reply:

Similar Artilces:

How to convert Oracle native Date to date time
I get numbers like 1178002042 instead of date time info when I run access againt oracle. Does anyone know what the math is to convert this to date time, I assume it is telling me the number of seconds past some arbirtrary point in time like midnight 1-1-1980 ? Does anyone know what this arbitrary point in time is, if so I can work with the date time info as delivered via my current connection and applications. Thanks On May 3, 7:55 am, tracy <usatr...@hotmail.com> wrote: > I get numbers like 1178002042 instead of date time info when I run > access againt oracle. > > Does...

database date-time variant to date-time in seconds, for numeric comparison operations
I am building a user&nbsp;login vi with Labview 8.2 that checks whether stored date/time values in the user record (MS SQL Server Express) have expired.&nbsp; I retrieve data/time values from the database as variants and use the database variant to data vi wired to a string data type, getting a mm/dd/yyyy hh:mm:ss AM/PM output string.&nbsp; I can convert this to a numeric date/time for comparison to the current date/time by parsing the string and using the cluster to numeric date/time vi.&nbsp; In testing of this, sometimes I get off by an hour, due to DST confusion I suppose.&...

oracle date time
How do you reference an oracle datetime when retrieving data for a given month from the oracle db using proc sql pass through? I need the datetime field in the sas dataset for calculations. What I found was when I was using a where clause that contained the following syntax: where date_field between '01JAN07'd and 31JAN07'd it would not return all the rows for that month arbritarily cutting off the month at the end of Jan 30. I tried the following but did not work. proc sql; credentials etc ----:> removed from this example create table xxxxx as select * from connection ...

Getting the Date and Time From a Time Server
Hello everyone, Is there a way to use the Import function to grab the current date and time from a time server on the Internet? Regards, Gregory Maybe you could run the "ntpdate" program external and retreive the time offset between your computer clock and the time server. (in the following example, the time server is only good in canada) Run["ntpdate -q time.chu.nrc.ca >/tmp/toto"] res=Import["/tmp/toto", "Table","FieldSeparators" -> {" ", ","}] offset=res[[1,6]] (* not very portable... :-( *...

Convert Character Date/Time to Text Date/Time
I have a column with date and time (order_time) that i would like to separate out and create a numeric date and a numberic time out of it. I have been able to split up the two using the SCAN function, but cannot figure out how to convert the two columns into text: Original Column: 2001-04-20 14:30:00 New Date Column: 2001-04-20 (char) New Time Column: 14:30:00 (char) Piece of cake: Data _null_ ; order_time = "2001-04-20 14:30:00" ; Format Date date9. Time time. ; Date = input( scan( order_time , 1 , " " ) , yymmdd10. ) ; Time = input( scan( order_time ,...

Help with comparing date stamp in PHP to date AND time stamp coming from MYSQL db!!!!!!
Hi all- I've searched for at least an hour tonight before posting this question into here. It's one of those questions that seemed simple AT FIRST, but I'm having a hard time filtering the info I get on all the date formats and how to compare them for my little script. This script is to disallow a user into an area who has been banned for a certain amount of time. So when the user logs in again, we compare the current date to the one the admin put into MYSQL and come up with an answer (that it is the date they are allowed back in or not). Also, I have no control over how the da...

Re: creating a date time variable from separate date and time
On Wed, 9 Jan 2008 07:54:37 -0800, Bill West <wm_a_west@YAHOO.COM> wrote: >Hello, > >I have a file with a date variable and a time variable but can't find >way to create a datetime variable by combining the two variables. Any >suggestions would be appreciated. hi, Bill, Just muliply 86400 to the date and add time. cheers, chang data _null_; format date date. time time. datetime datetime.; date = '9jan2008'd; time = '11:00am't; datetime = 86400* date + time; put (_all_) (/=); run; /* on log date=09JAN08 time=11:00:00 datetime=09JAN08:11:00:0...

Re: Convert Character Date/Time to Text Date/Time
Hi Mark, You mention in the text you want numeric variable and the example following denotes that you want character, so I will do both: data sample; Original = '2001-04-20 14:30:00'; run; * to make character variables ; data result1; length New_Date_Char $10 New_Time_Char $8; set sample; New_Date_Char = scan(Original,1,' '); New_Time_Char = scan(Original,2,' '); run; * to make numeric variables ; data result2; length New_Date_Num 8 New_Time_Num 8; set sample; New_Date_Num = input(scan(Original,1,' ...

Date, date date date....
Date is driving me crazy. I simply need to calculate the age of a person: this is what doesn't work: public int getAge(Person person) { long d = new java.util.Date().getTime(); long m =person.dateOfBirth().getTime(); long l=d-m; SimpleDateFormat formatter = new SimpleDateFormat("yy"); return (new Integer(formatter.format(age))).intValue(); } I'm getting 91 for a person who's born in 1983 Since getYear() is depreciated I don't like to use that I found that Calender could do the job, but how do I cast Date into Calender in an easy way? hin...

creating a date time variable from separate date and time fields
Hello, I have a file with a date variable and a time variable but can't find way to create a datetime variable by combining the two variables. Any suggestions would be appreciated. Thanks, Bill West wm_a_west@yahoo.com /* use zero placeholders for the H and M parameters */ sasdt=3Ddhms(sdate,0,0,stime); little example below data me; mdate=3D'10JAN2008'd; mtime=3D'16:04't; mdtime=3Ddhms(mdate,0,0,mtime); format mdtime datatime.; run; proc print data=3Dme; run; On Jan 9, 3:54=A0pm, wm_a_w...@yahoo.com wrote: > Hello, > > I have a file wit...

Oracle insert date and time
I have a JDBC current date and time insert into Oracle 9i that almost works. It submits the current date and a fixed time into the Oracle date type field. I am using Tomcat 6.0.20. For example if I insert the data at 7:24:04 PM on Feb 16, 2010 it will insert as: 16-Feb-2010 12:00:00 AM The date part works but the time always shows 12:00:00 AM no matter what date or time the data is inserted. Here is what I have for my JDBC inserts and I also tried something with DateFormat: PreparedStatement ps; Date mydate = new Date(new java.util.Date().getTime()); //insert statement here.... stmt.setDate(...

Study Date/Study Time and Series Date/Series Time
Hi, I see that neither Study Date/Study Time nor Series Date/Series Time are mandatory in most image IODs (well, Study Date/ StudyTime is type 2). Does anybody have a feeling for how many modalities do NOT send them? Maybe Series Date/Series Time is more frequently not sent (they are type 3). Thanks for any feed-back! ...

Re: oracle date time
I've found that the most reliable way to do this is to use the Oracle function TO_DATE: TO_DATE('2006-01-01', 'YYYY-MM-DD'); On Jun 13, 2008, at 12:05 pm, sheridany wrote: > How do you reference an oracle datetime when retrieving data for a > given month from the oracle db using proc sql pass through? > > I need the datetime field in the sas dataset for calculations. What I > found was when I was using > > a where clause that contained the following syntax: > > where date_field between '01JAN07'd and 31JAN07'd it would not r...

Re: Convert Character Date/Time to Text Date/Time #2
Mark instead of busting things apart try using an input statement with the supposedly undocumented informat YMDDTTM Date = input( cdate , ymddttm20. ); Someone posted this informat on SASL a year or so ago. If you really want to bust it up, you could do so and then use input statements with the informats mmddyy10. and Time8. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 Mark Sussman <msussman@GMAIL.C OM> ...

Re: creating a date time variable from separate date and time #2
Bill Look at the dhms function DHMS (d,h,m,s) sas datetime from date, hour, minute, and second You may need to extract the hour and minute from the time and fake the second. Nat Wooding Environmental Specialist III Dominion, Environmental Biology 4111 Castlewood Rd Richmond, VA 23234 Phone:804-271-5313, Fax: 804-271-2977 Bill West <wm_a_west@YAHOO. COM> To Sent by: "SAS(r) SAS-L@LISTSERV.UGA.EDU Discussion" ...

Comparing Dates Time
Hi I am using the three controls, Calendar, datetext, timelist and creating a date after concatenating them and then comparing to the Current date/time. User enters the information and then this is done If Format(datetext & " " & timelist, "mm/dd/yyyy hh:mm") < Format(Now, "mm/dd/yyyy hh:mm") Then Msgbox "Earlier Date" Else Msgbox "Later Date" End If It was working fine till the dates were to be entered for 2007. So I am selecting a date in 2007, I get earlier date in the above expression. How can I fix that? Any ideas will...

Plotting date-time series in 3D how to handle date-time to plot
I have a year's worth of time series data that I would like to plot in 3d using ListPlot3D. ListPlot3D does not accept the system formatted dates like the 2D utility DateListPlot. Q1. What is the best way to plot this type of time series in 3D where the date-times may cross January 1 and two or more years? A week's worth of data is appended. I have previously been doing this by converting raw csv data file's date-time to a Julian Day in Excel and then importing to Mathematica. I would like to simplify things by doing the entire import and plot routine in Mathematica dir...

Re: Convert Character Date/Time to Text Date/Time #4
in SAS9 there is an informat for almost everything. A small data step revealed these 7 informats return the correct timestamp fmtname=ANYDTDTM fmtname=B8601DJ fmtname=B8601DT fmtname=E8601DT fmtname=IS8601DT fmtname=ND8601DT fmtname=YMDDTTM Once we find the suitable format names , it is easy to read up about their full capabilities in the online help. To perform the search, I adapted some old code. Try this on your SAS environment option errors=1; data sample; retain Original '2001-04-20 14:30:00' ; set sashelp.vformat ; where fmttype = 'I' & fmtnam...

Does the Get Date/Time In Seconds vi give the time when the...
Does the Get Date/Time In Seconds vi give the time when the system clock finished the last second, or does it just have one-sec resolution? I am needing an accurate assesment of the computer's clock. The function will return a value with a 1 ms resolution. The true resolution and accuracy of it is another question though. Try calling it twice with a wait in between that you vary and you'll get an idea... Check out the following thread: http://messages.info-labview.org/2003/01/28/04.html If the function returns only integer seconds, and you say that this is to within 1 ms of some s...

How to get ORACLE Server Date, not local PC Date?
Hi, I'm trying to get the server date, but sysdate or any date function returns the date of my PC ... is there any way to get this? Alesk "alesk" <find_who@hotmail.com> a �crit dans le message de news: 1152210477.335697.232010@s26g2000cwa.googlegroups.com... | Hi, | | I'm trying to get the server date, but sysdate or any date function | returns the date of my PC ... is there any way to get this? | | Alesk | From doc. <quote> SYSDATE returns the current date and time set for the operating system on which the database resides. </quote> Regards Michel Ca...

Insert time into Oracle date field
Recently I try to migrate application from Unify database into Oracle. I came cross a problem. In Unify database there is time field which can store time value like 13:45. I am just wondering if it's possible to insert this time value into Oracle date field and how to insert it. Thanks in advance Alan "Alan" <wangxianlg@yahoo.com> wrote in message news:18a6fc2f.0405241654.264a90a3@posting.google.com... > Recently I try to migrate application from Unify database into Oracle. > I came cross a problem. In Unify database there is time field which > can store time ...

How to update time in a date column in oracle
Hi I have a date column in one of the table in oracle. The date value stored is: 7/5/2006 2:11:00 PM. I would like time to be updated to 6PM so that the date value will look like: 7/5/2006 6:00:00 PM. Can anybody please help. Thanks Pankaj pankyrai wrote: > Hi > > I have a date column in one of the table in oracle. The date value > stored is: > 7/5/2006 2:11:00 PM. > > I would like time to be updated to 6PM so that the date value will look > like: > 7/5/2006 6:00:00 PM. > > Can anybody please help. > What have you tr...

Oracle insert current date and time
I have a JDBC current date and time insert into Oracle 9i that almost works. It submits the current date and a fixed time into the Oracle date type field. For example if I insert the data at 7:24:04 PM on Feb 16, 2010 it will insert as: 16-Feb-2010 12:00:00 AM The date part works but the time always shows 12:00:00 AM no matter what date or time the data is inserted. Here is what I have for my JDBC inserts: PreparedStatement ps; Date mydate = new Date(new java.util.Date().getTime()); //insert statement here.... stmt.setDate(1,mydate); I also tried: PreparedStatement ps; java.sql.Timestamp m...

Oracle datatype DATE and time zone
Does oracle datatype DATE stores timezone as well ?? or we need to use datatype TIMESTAMP WITH TIME ZONE Thanks in advance, Anil G Why do you want/need to adjust for the timezone? Depending on your answer besides the timestamp with time zone datatype you might be able to use a date datatype with a column to indicate the time zone, or to use a before insert/update trigger to modify the date value for the timezone offset. HTH -- Mark D Powell -- ...

Web resources about - Get the date time from Oracle date time stamp????? - comp.databases.oracle

Ration stamp - Wikipedia, the free encyclopedia
Rationing has been perpetual in India since World War II. The type of ration card is an identifier to the benefit eligibility of the holder. ...

ANU pledges to stamp out sexual harrassment
The ANU is committed to providing opportunity equally to women and men, equally to Indigenous and non-Indigenous Australians, equally to people ...

Stamp duty plea for unit buyers, seniors
STAMP duty reductions for off-the-plan apartment buyers and downsizing seniors should be introduced at the next State Budget, according to one ...

Rare 'emergency' 30 cent stamp from Adelaide now worth thousands
How a stamp printed earlier this month and valued at 30 cents has taken the world, and space, by storm

U.S. issues Year of Monkey commemorative forever Stamp(1/5)
Ecns.cn is the official English-language website of China News Service (CNS), a state-level news agency sponsored and established by Chinese ...

Maine shows how to cut Food Stamp fraud
The fastest growing category in the fast growing Food Stamp program – now the second most expensive welfare program of the feds – is able-bodied ...

No more surf and turf on food stamps?
Two New York state senators want to prevent people from using food stamps to purchase junk and luxury food items.

How to Stamp Out Creativity
We've all had experiences of environments that were not conducive to creative thinking. For starters, a good indication is the lack of enthusiasm ...

Arizona Couple Goes To Jail For Food Stamp Scheme
'Anyone who lies to obtain public benefits is stealing from the taxpayers of Arizona'

Republican congressman pushes drug testing for food stamps
Drug testing for government assistance is a big trend among Republican state legislators and governors over the last few years, and they’ve run ...

Resources last updated: 2/20/2016 3:07:08 AM