I have a source as flat file and the date is as 15-AUG-11 12.00.00.000000000 AM.
My target is a regular oracle table with date as integer column.
Appreciate if some one can help in converting the date (string) in the file to integer in target.
You have to convert the date from flat file to Date format ("DD/MM/YYY 24HHMMSS") then convert this to String format like ("DDMMYYYY24HHMMSS") and then convert the string format to Integer format.
The end expression could look like this
To_Integer(To_Char(To_Date(testdate,"DD/MM/YYYY 24HH:MM:SS"), "DDMMYYY24HHMMSS"))
Hope this helps.
Kindly let me know if this helps.
To_integer function will work only if there is no character in the feild value .Else it will return Zero after conversion.
You need to extract the month, date and year seperately and then append the date,month and year.
Finally convert that to interger using to_int function
Thank you so much for your response.
Here is how I fixed :
Input : 15-AUG-11 12.00.00.000000000 AM.
Expected Output : 20110815.
1. First I truncated the source column 'SERVICE_DATE' to 15-AUG-11
2. Converted to TO_DATE(SERVICE_DATE,'DD-MON-YY') in a variable with Datatype date/time. Note : we have only YY and not YYYY from the source.
3. Then TO_INTEGER(to_char(v_SERVICE_DATE,'YYYYMMDD')) with Datatype integer.