RE: Date fields store as varchar2???

Jared - Excellent, excellent reply. Ralph Kimball would be proud of you. Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, April 19, 2002 7:43 PM
To: Multiple recipients of list ORACLE-L

Storing dates as varchar2 doesn't sound like a good idea to me, especially in a data warehouse.

All date comparisons will require converting to date type.

In many cases you probably don't want to stored dates in your data warehouse anyway. They should be in a robust date dimension table. The date component is used to determine the key to the date dimension table at load time. If you want to keep the time component in the DW you will need to determine the granularity required and make it a part of your date dimension for consistency.

That could conceivably create a rather large date dimension.

Granularity for 10 year date dimension:

1 day: 3652 rows
1 hour: 87,648 rows
1 minute: 525,880 rows
1 second: 315,532,800 rows

That last one is a seriously large dimension! A little too big for practicality.

> 1) Snapshots - if the date in the data WH is going to
> be refreshed via a snapshot, what will the value be.
GMT
> 2) Export/Import

GMT
> 3) SQLLoader

GMT Your DW in EST will not know that these are EST dates.

Prior to 9i, Oracle really is not too aware of TZ. I haven't yet explored what 9i will do though in the way of TZ.

Hope you aren't querying your DW with 'sysdate'. :)

Jared

On Friday 19 April 2002 12:28, Celine John wrote:
> Hello,
> Our DB has a GMT time zone, while our data
> warehouse has EST.
>
> For our application, some guys here insist that we
> store dates as varchar2 to avoid all the problems
> associated with data in the two databases.
>
> Though personally I want the dates to be stored in
> oracle date datatype, I would like to know more about
> how date fields behave in different processes...
> like
> 1) Snapshots - if the date in the data WH is going to
> be refreshed via a snapshot, what will the value be.
> 2) Export/Import
> 3) SQLLoader
>
> I depend on this list to give me some expert advice on
> this.
>
> Thanks much.
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Tax Center - online filing with TurboTax
> http://taxes.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).