Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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.

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational