Re: Date Help

ORACLE has powerful date functions that are described fully in their 'SQL Langauge Reference Guide'. Always bear in mind that an ORACLE date has a time element, which by default (if not specified) is set to 00:00 (HH:MI format) - midnight. As an introduction:

1. You can apply a date format mask to a date column / field to display it in virtually any format you like using the TO_CHAR function is SQL or Format masks or equivalent in Forms.

e.g. say you want to get the 4 digit year (YYYY), month as digits (MM) and day as digits (DD) for a sample column called 'sample_date' which is of DATE format, you can select (in SQL) the following:

	SELECT TO_CHAR(sample_date,'YYYY')
		,TO_CHAR(sample_date,'MM')
		,TO_CHAR(sample_date,'DD')
	FROM DUAL;

or 
	SELECT TO_CHAR(sample_date,
			'FmDay Month ddth, YYYY')
	FROM DUAL;

would return (for sample_date := '06-JUN-97')

        'Friday June 6th, 1997'

2. There are STANDARD packages supplied by ORACLE such as:

ADD_MONTHS
TO_CHAR (as above)
TO_DATE
TRUNC -
ROUND - these are powerful when applied to dates in conjunction with a date format mask e.g. to find the date of the first Monday in a date's month
+ others - see guides under 'Date functions'

3. You cannot add dates together directly, but you can add a delta datetime to a date as a number e.g. 31-JAN-97 (date) + 2.5 = 02-FEB-97 12:00. . .  

4. You can subtract them which returns the number of days (and time - as expressed as part of 24hrs) beween them e.g.

	SELECT
	TO_DATE('03-JAN-97', 'DD-MON-YY') -
	TO_DATE('01-JAN-97 12:00','DD-MON-YY HH24:MI')
	from dual;

	would give an answer: 1.5

3. You could develop your own API's as required.

4. In the database setup the default database date format is defined by NLS_DATE_FORMAT and is typically DD-MON-YY, though ORACLE recommends this is modified to DD-MON-RR for Y2000 compliancy purposes. (see manuals for full explanation of -RR format masks). You can modify this at transaction level though if you want to.

I suggest studying the SQL Reference manual, but please feel free to email me if you want specific help.

Regards

• Martyn Cavett Senior ORACLE Analyst LGT Asset Management London, UK. home: cavett_at_globalnet.co.uk work: Martyn.Cavett_at_gtplc.com

These views are my own and not those of my employer. Normal conditions apply.

Doermann, Jochen <jochen.doermann_at_doit.wisc.edu> wrote in article <3397083C.D7FA203A_at_doit.wisc.edu>...
> We're just getting our feet wet with Developer/2000 and Forms 4.5
> (although it sometimes feels more like we're drowning). I need some
> help with date functions.
>
> In other languages I'm used to functions like YEAR(a_date) that returns
> an integer date value. (Also MONTH(a_date) and DAY(a_date)). I can't
> find an equivalent in Oracle.
>
> Ultimately, what I want to do, is break a date apart, manipulate the
> individual parts, and put it back together again. Any suggestions
> and/or code snippets?
>
> TIA -- Jochen
  Received on Fri Jun 06 1997 - 00:00:00 CEST