Storing a zero-date in Oracle.

[Cheek Bob]

Rich,

There is no way to do it with SQLPlus syntax because 00/00/0000 is not a
real date.  PH can get away because it uses Oracle's OCI layer.  There is a
technique around this though.

Create a table that has one key field in it and one date field.
With Powerhouse's QTP set the date to zero.  That will get a table with a
zero date in it.  The key field will give you a field you can link to. 

Now whenever you are in SqlPlus you can link (join) to the zero date table
and set whatever date you want to set to zero by setting it to the date from
this zero table.

Bob Cheek
Senior Technical Advisor
Visibility Inc.
Cheek@Visibility.com <mailto:Cheek@Visibility.com> 
978.694.8180 Voice
978.694.8020 Fax
Check Out our web page: http://www.visibility.com
<http://www.visibility.com> 

	-----Original Message-----
	From:	Rich `Lego-Man' Jesse - 7633 [SMTP:RJESSE@QTIWORLD.COM]
	Sent:	Monday, May 17, 1999 11:50 AM
	To:	oracle@telelists.com; powerh-l@lists.swau.edu
	Subject:	Storing a zero-date in Oracle.

	(this message is being cross-posted to oracle-l and powerh-l)

	Hi,

	I'm trying to determine how to store a zero date in Oracle.  I'm not
looking
	for 01/01/4712 BC, but 00/00/0000.

	Why?  Because our software vendor does not support nulls in date
fields, and
	the 4GL software that our app was written in, PowerHouse 733D3
(specifically
	QTP), stores blank dates as "00/00/0000 00:00:00.000".  I can
retreive the
	dates using any number of tools, but I can't for the life of me
figure out how
	to duplicate the population of the date fields in SQL without
getting
	conversion errors.

	Any ideas?

	TIA,
	Rich Jesse                              Programmer/Analyst
(/DBA/SysAdmin/etc.)
	rjesse@qtiworld.com                     Quad/Tech International,
Sussex, WI USA
	= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= = = =
	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.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
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.