You can assign absolute date or interval column values to a date column. In addition, you can assign a string literal, a character string host variable, or a character string column value to a date column if its value conforms to the valid input formats for dates.

When you assign a date value to a character string, the DBMS Server converts the date to the display format. For more information about date display formats, see Date and Time Display Formats.

You can use the date_trunc function to group all the dates within the same month or year, and so forth. For example:

returns "1-oct-1998", and

returns "1-jan-1998".

Truncation takes place in terms of calendar years and quarters ("1-jan," "1-apr," "1-jun" and "1-oct").

To truncate in terms of a fiscal year, you must offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year ("6 mos" for a fiscal year beginning July 1, or "4 mos" for a fiscal year beginning September 1):

Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.

This function is useful in set functions and in assuring correct ordering in complex date manipulation. For example, if date_field contains the value
23-oct-1998,

returns a value of "10" (representing October), and

returns a value of "23".

Months are numbered 1 to 12, starting with January. Hours returned according to the 24-hour clock. Quarters are numbered 1 through 4. Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0.