Oracle dates

[Jones Allen (Van)]

Thank you to all who responded.

Our application already has separate date and time columns, as it did before
it was ported to Oracle.  Now we are adding functionality that requires
access by Powerhouse and Oracle stored procedures.    I want to use Oracle's
features as much as possible in new development.  The application involves
many calculations of sums and differences between one date/time and another
and Oracle's functions have built-in capability to do this easily if both
the date and time are stored in one column.  

I didn't try an Oracle DEFAULT on the column because the date column is the
only one I'm changing in this particular application, so the Powerhouse
program must provide the data.  Also,  the default is only applied for new
rows, and not changed rows, and the trigger I'm writing depends on a change
to the date value.  However, I'm sure this solution would work for "create
date" stamps, and I'll keep it in mind.

Solutions I tried that work are:
		from Richard Waddicor:	temporary t_sys_datetime
datetime reset at startup
						... procedure init
						 let t_sys_datetime =
sysdate + systime/100000000

		from Paul Howard:		 date(days(sysdate) +  &
				   (((floor(systime / 1000000) * 3600) + &
				     (floor(mod(systime, 1000000) / 10000) *
60) + &
				     (floor(mod(systime, 10000) /100))) /
86400))

I adapted Richard's approach because it's simplest and now have:

item ORACLE_DATE_COLUMN   final sysdate + systime / 100000000

The sqlplus select statement:
          select to_char(ORACLE_DATE_COLUMN,'DDMonYYYYHH24MISS') from ...
where ...
gives  04Feb1999092718

and the Quiz report statement
          report ORACLE_DATE_COLUMN pict "^^^^^^^^^^^^^^" out scale 6
gives  19990204092718

Again, thank you all.

Allen Jones
VantagePoint Group



= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.