Convert Varchar2 to Date Format

[perlcamp]

I am trying to convert a varchar2 field into a specific date. The values in the column have the format of 01/23/2008 and I would like it to be 2008-01-23.

I have tried select to_date('column_name', 'YYYY-MM-DD') but I get an error returned from Oracle that it is an invalid month.

Appreciate the help.

 

[Thargy]

perlcamp,

your question is a bit confusing.

If you're trying to convert a string to a date, then you should use the TO_DATE function with an appropriate mask. Then insert the TO_DATE of your string into a date field. You may then extract those dates in any desired format using TO_CHAR etc.

If you're trying to alter the format of your string, and leave it as a string, then just create a view of what you want.

I feel obliged to point out that storing dates as strings is usually an unmitigated disaster. Can you tell us why a date is being stored in a string?

Regards

T

 

[perlcamp]

First I know storing dates in a varchar is bad, hence this disaster. I get stuck with what was left behind.

All I need to do is pull data out of this table for a report but I need the date to be in the format of 2008-01-23. I tried to use the to_date function with the format specified as noted above but I get the invalid month error.

I don't have the option of creating a view, this needs to be a straight select.

Can this be done?

 

[dkyrtata]

If you want to display a string as 'YYYY-MM-DD' try one of these:
TO_CHAR(TO_DATE(string_column,'MM/DD/YYYY'),'YYYY-MM-DD')
SUBSTR(string_column,7,4) || '-' || SUBSTR(string_column,1,2)||'-'|| SUBSTR(string_column,3,2)

If you want to manipulate a sring as a date (such as adding a certain number of days to it) try one of these:
TO_DATE(string_column,'MM/DD/YYYY')
TO_DATE(SUBSTR(string_column,7,4) || '-' || SUBSTR(string_column,1,2)||'-'|| SUBSTR(string_column,4,2),'YYYY-MM-DD')

You need to remember whenever you use the TO_DATE() function, you must tell the function how to parse the string so that it knows where to get each of the date components. That is why you need to specify the correct format for the second argument of the function (i.e the format corresponding to the source string). Once the string is converted to a date, Oracle internally stores it as a number.

For example Oracle may hypothetically store '01-Jan-1900' as the number 1, while the next day would be 2, etc. That is how date manipulation is made simple: Oracle simply adds and subtracts numbers. The resulting number is just another date that can be converted to a meaningful date-string via the TO_CHAR() function. If you omit using the TO_CHAR() function, Oracle will display the date in the default format of the SQL session (which can be changed with the ALTER SESSION command)

I hope this makes sense

 

[SantaMufasa]

Once the string is converted to a date, Oracle internally stores it as a number.

Although other database engines may store dates as a displacement from some specific "starting date" (such as January 1, 1970), Oracle stores DATE expressions as an 8-byte (internal) data item with the following components:


Sign: + = A.D.; - = B.C.
2-digit Century (e.g. "20")
2-digit Year (e.g. "10"
2-digit Month (e.g. "09")
2-digit Day (e.g., "20")
2-digit 24-hr. Hour (e.g., "15")
2-digit Minute (e.g., "38")
2-digit Second (e.g., "14")
2-digit Hundredths of a Second (e.g., "68")

Oracle's DATA limits are "January 1, 4712 B.C." to "December 31, 9999 @ 11:59:59.99".

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”