Re: century in date field?

In <31tp9n$59a_at_paperboy.gsfc.nasa.gov>, larson_at_sled.gsfc.nasa.gov (Teresa A Larson) writes:
>In article <31r53m$hki_at_homer.cs.mcgill.ca>, jennifer_at_cca.qc.ca (Jennifer Waywell) writes:
>|> Is it possible to alter the defaults and allow a date field to accept a
>|> date with the century (ie '14-JAN-1672') or must you make it a CHAR field
>|> and use the TO_DATE function each time that you want to do a calculation
>|> involving the date?
>|>
>
>The DATE datatype actually stores 2 pieces of information -- the date
>and the time. When you are SELECTing columns of type date, use the
>TO_CHAR function in the SELECT clause and use TO_DATE in the WHERE
>clause, if needed. When you are INSERTing or UPDATEing dates you use
>TO_DATE.
>
>When you read up on these 2 functions, you'll notice
>that both of the have a format as part of their syntax
>
> TO_CHAR('date info', 'format')
> TO_DATE('date info', 'format')
>
>The format masks allow you to display and handle the date and/or time
>in almost any arrangment you want.
>
>The documentation describes all the format pieces-and-parts. To SELECT
>date information in the format you mention above, for example:
>
> SELECT TO_CHAR(column_name, 'DD-MON-YYYY')
> FROM table_name
> WHERE column_name < TO_DATE('31-JAN-72', 'DD-MON-YY')
>
>I just gave the WHERE clause above to show you can mix-and-match if
>needed.
>

Also look at "alter session set NLS_DATE_FORMAT = '?????'. We use:

alter session set NLS_DATE_FORMAT = 'YYYYMMDD' so all our dates come back in a better format for programming...

  Chad

--
Chad Brockman            | 
VertiComp, Inc           | I'm just an OS/2 Junkie!!
chadb_at_NeoSoft.com        | "Come on kid, it's cheap!!"