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.
