Oracle Datefunctions

Alongside numerical values and character strings, the commonly used data type ‘date’ exists for handling dates and timestamps. The date can be processed with millisecond precision. Below, I have listed the Oracle date functions for date calculations, which I frequently use.

Formatting mask

The Oracle functions for date calculation and conversion sometimes require a formatting mask (format_mask), indicating the position of each part of the date within the string for conversion/calculation. The following table contains only a small portion of the possible parameters, as I rarely use other parameters:

Parameter	Description	Example
YYYY	Year 4 digits	2008
YY	Year 2 digits	08
MM	Month	11
WW	Week of year	51
D	Day of week (1-7)	3
DD	Day of Month	15
HH24	Hour (24h-Format)	13
MI	Minute	45
SS	Second	34

to_date()

With the function to_date(), a date can be generated from a string. This is done by determining, based on the above formatting mask, how the date is structured within the string. The to_date() function creates a date if it is valid; otherwise, it terminates with an error. Examples:

• select to_date('02.10.2003','DD.MM.YYYY') from dual;
• select to_date('01.01.2010 15:45','DD.MM.YYYY HH24:MI') from dual;

to_char()

The function to_char() converts a date back into a character string. Similar to to_date(), it uses the same parameters. However, instead of a string with a date, the date variable or date field is passed. Examples:

• select to_char(sysdate,'YYYY') from dual; — Display current year
• select to_char(sysdate,'HH24:MI:SS') from dual; — return current time

Working with Months

The examples above cover simple conversions. For months, you can use the last_day() function to determine the last day of the month from a passed date. A date is returned again:

• select last_day(sysdate) from dual; – Show last day of date of current month
• select to_char( last_day(sysdate) , 'DD') from dual; – return only last day of month of current month

It’s easier to determine the first day of the month. Oracle has not provided a function for this

• select to_date( sysdate, '01.MM.YYYY') from dual; — Return date as string with first day of month.

Since the first day of the month is always 01, the value 01 is entered in the function mask instead of DD, as this is not interpreted but adopted directly.

The add_months() function adds any number of months to a date. Example:

• select add_months( to_date('04.05.2010','DD.MM.YYYY'), 4 ) from dual; — Display Date 04.09.2010

Easy calculation

Any number can be added or subtracted to a date. The value 1 corresponds to one day. For example at

• select sysdate + 1.5 from dual;

a date is returned which has been increased by 1.5 days or 36 hours.