Q
Problem solve Get help with specific problems with your technologies, process and projects.

Converting from CHAR to DATE datatype

As a time value, I used the CHAR datatype and need to convert to a DATE value while manipulating/computing. How do I use the DATE datatype instead?

As a time value, I used the CHAR datatype and need to convert to a DATE value while manipulating/computing. How do I use the DATE datatype instead?
If you need to convert a CHAR or VARCHAR2 datatype to the DATE datatype, then you will have to use the TO_DATE function. For instance, to convert the characters "01/01/2006" to a date, I could use TO_DATE('01/01/2006','MM/DD/YYYY') in my SQL or PL/SQL statement. The first parameter to the TO_DATE function is the character string representing a date and time. The second parameter is called a format mask. You must tell the TO_DATE function how you formatted the string.

In my example, my format mask is "MM/DD/YYYY." There are many format masks available and you can view them here:

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924

The inverse of the TO_DATE function is the TO_CHAR function, which takes a DATE datatype value and converts it to a character string. Again, you have to specify the format mask of the output character string.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close