Problem with date datatype.oracle?

Truman Biby: Inserting from a string is the most common and the easiest way to fill a DATE in Oracle. The key point to watch is that the string is interpreted using a default format. That format is defined by the current locale (language and territory) setting for the database session that does the insert. That means that the insert will fail if the user has a different locale than the one you think (for example a French user and an American user will have different locales)So the safest way for dealing with dates is to ALWAYS use an explicit format. That will make your insert independent from any locale changes. It will also let you choose the best format for the input date. For example, imagine your input for a date is '20121004' (year, month, day) and it is in a string variable "my_date". To insert it, use this:TO_DATE (my_date, 'yyyymmdd')The same applies to reading a date. Here too the conversion will use default rules that may not be what you want. So use an explici! t format. For example, the following will return the date in the same format as above:select to_char (order_date, 'yyyymmdd') from orders;If you want to get the date like this: 04-Oct-2012, use this format:select to_char (order_date, 'dd-Mon-yyyy') from orders;Notice the capital letter in "Mon". If you write MON you get OCT.You can use this approach to extract additional information from a date:select to_char (order_date, 'Day dd-Mon-yyyy') from orders;returns "Thursday 04-Oct-2012"Finally you can use it to extract just one element from the date, for example just the year.Or the day of week. Read about the codes at: http://docs.oracle.com/cd/E11882_01/server.112/e26......Show more