Re: century in date field?

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.

				Hope this helps
				Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson - Hughes STX Corporation                            |
| NASA/GSFC Code 933.0                        voice:  (301) 286-7867   |
| Greenbelt, Maryland  20771                  fax:    (301) 286-1777   |
| Teresa.Larson_at_gsfc.nasa.gov                                          |
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+