To Date

Oracle to_date usage tips Oracle Tips by Burleson Consulting Question: How do I use the to_date function to store data into a DATE datatype. Answer:  The to_date function is used to convert character data to the date datatype. Like to_char, this function can be called with a single parameter, much like to_date ('02-MAY-97') which returns a value of type date. to_date may also be called with a second parameter, which instructs the function to convert the specified string from the specified format into a standard date. For example, to_date ('02 May 1997', 'DD MONTH YYYY') returns 02-MAY-97 The Oracle to_date function is used to change a test string (or variable) into an internal date format. Remember, all DATE datatypes are stored in a special internal format, complete to the hundredth of a second.  You can change the display using nls_date_format also. The to_date function is usually used in SQL when storing into the database. Insert into mytab (date_col) values to_date(string,format); Examples of the to_date function might include: to_date('10-12-06','MM-DD-YY') to_date('jan 2007','MON YYYY') to_date('2007/05/31','YYYY/MM/DD') to_date('12-31-2007 12:15','MM-DD-YYYY HH:MI') to_date('2006,091,00:00:00' , 'YYYY,DDD,HH24:MI:SS') to_date('15-may-2006 06:00:01','dd-mon-yyyy hh24:mi:ss') to_date('022002','mmyyyy') to_date('12319999','MMDDYYYY') to_date(substr( collection_started,1,12),'DD-MON-YY HH24') to_date('2004/10/14 21', 'yyyy/mm/dd hh24') TO_DATE(First_Load_Time, 'yyyy-mm-dd/hh24:mi:ss'))*24*60) The docs show us the different format masks for the to_date function: Element Specify in TO_DATE? Meaning - / , . ; : 'text' Yes Punctuation and quoted text is reproduced in the result. AD A.D. Yes AD indicator with or without periods. AM A.M. Yes Meridian indicator with or without periods. BC B.C. Yes BC indicator with or without periods. CC SCC No One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-". For example, '20' from '1900'. D Yes Day of week (1-7). DAY Yes Name of day, padded with blanks to length of 9 characters. DD Yes Day of month (1-31). DDD Yes Day of year (1-366). DY Yes Abbreviated name of day. E No Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). EE No Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). HH Yes Hour of day (1-12). HH12 No Hour of day (1-12). HH24 Yes Hour of day (0-23). IW No Week of year (1-52 or 1-53) based on the ISO standard. IYY IY I No Last 3, 2, or 1 digit(s) of ISO year. IYYY No 4-digit year based on the ISO standard. J Yes Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers. MI Yes Minute (0-59). MM Yes Month (01-12; JAN = 01) MON Yes Abbreviated name of month. MONTH Yes Name of month, padded with blanks to length of 9 characters. PM P.M. No Meridian indicator with or without periods. Q No Quarter of year (1, 2, 3, 4; JAN-MAR = 1) RM Yes Roman numeral month (I-XII; JAN = I). RR Yes Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50. RRRR Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, simply enter the 4-digit year. SS Yes Second (0-59). SSSSS Yes Seconds past midnight (0-86399). WW No Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. W No Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. Y,YYY Yes Year with comma in this position. YEAR SYEAR No Year, spelled out; "S" prefixes BC dates with "-". YYYY SYYYY Yes 4-digit year; "S" prefixes BC dates with "-". YYY YY Y Yes Last 3, 2, or 1 digit(s) of Get the Complete Oracle SQL Tuning Information The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

knighttroich85.blogspot.com

Source: http://www.dba-oracle.com/f_to_date.htm

Share this post