TIPS IT Consulting

Note: TIPS IT Consulting acquired TIPS BF Consulting on Jan. 6, 2011

Oracle Date function

Oracle supports both date and time differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second. Oracle converts the date value from its special internal format to to a printable string via the TO_CHAR function.

Please note 1) that tableName is the name of your database table name
2) that columnName is the name your database column

Example 1) select * from tableName WHERE columnName = to_date('01-01-1999', 'DD-MM-YYYY')
Example 2) select to_char (columnName, 'YYYY/MM/DD') AS b from tableName

Example 3) insert into tableName (columnName) values(to_date('1998/05/31:11:10:05',
'YYYY/MM/DD:HH:MI:SS AM'))
Example 4) update tableName set columnName = to_date('07-25-2010', 'MM-DD-YYYY')
where columnName = to_date('06-25-2010', 'MM-DD-YYYY')

Here is the table showing the meaning of the different to_date values.

`MM`	Numeric month (e.g., `07`)
`MON`	Abbreviated month name (e.g., `JUL`)
`MONTH`	Full month name (e.g., `JULY`)
`DD`	Day of month (e.g., `24`)
`DY`	Abbreviated name of day (e.g., `FRI`)
`YYYY`	4-digit year (e.g., `1998`)
`YY`	Last 2 digits of the year (e.g., `98`)
`RR`	Like `YY`, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, `06` is considered `2006` instead of `1906`
`AM` (or `PM`)	Meridian indicator
`HH`	Hour of day (`1`-`12`)
`HH24`	Hour of day (`0`-`23`)
`MI`	Minute (`0`-`59`)
`SS`	Second (`0`-`59`)