Re: Date and DateTime columns

Jim Poe wrote:
>
> Hi,
>
> Using Oracle 8, Delphi 5, Midas
>
> This is a design question. We have approximately 65 date fields. A
> little less than half (30) of them are required to be timestamps. The
> rest only need to be concerned with the date portion.
>
> We are having an internal debate on how to treat these fields.
>
> One faction wants to treat all the date columns the same and populate
> them with the date and current time, a timestamp. This would mean that
> all date columns would be handled consistently. It would require that
> any date only comparisons and selects would need to truncate the time.
> This would need to be done in our sql code, our Delphi application code,
> and any tools that the end user would use.
>
> The other faction wants to specifically class the two date types. All
> date only fields would have a naming convention of XXX_DT. They would
> be populated with the date and 00:00:00 time. In code we would always
> use
> MyDate := Date;
> The timestamp fields would have a naming convention of XXX_TS. They
> would be populated with a full date/time timestamp.
> MyDate := Now;
> When manipulating the dates, the developer and end user would know when
> they have to truncate time by the column name being in the format
> XXX_TS. Any XXX_DT columns could use the raw value for comparisons and
> selects.
>
> Are there any other issues we are not aware of? How do others handle
> this situation?
>
> Thanks
> --
> Jim Poe ( jpoe_at_fulcrumit.com )

What datatype are you propsing for the "XXX_DT" type columns? If you use DATE, you will not have solved anything. If you use a NUMBER or VARCHAR2 type, you will need to define a standard way to populate it, and of course train your entire world how to use it. IMHO, stick wit the default DATE datatype. Make ypur life simple and don't add to your own confussion.

-- 
Ron Reidy
Oracle DBA