Skip navigation
Contact Us
1298 Views 3 Replies Latest reply: Nov 14, 2012 6:20 AM by SRI SRI RSS
SRI SRI Novice 21 posts since
Jan 25, 2011
Currently Being Moderated

Nov 13, 2012 7:40 AM

Date Conversion from string to number

Dear Friends,

 

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.

 

Thanks,

Sri

  • Fahad Khan Newbie 2 posts since
    Feb 9, 2012
    Currently Being Moderated
    Nov 13, 2012 11:50 PM (in response to SRI SRI)
    Date Conversion from string to number

    Hi Sri,

     

    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

     

    testdate=15-AUG-11 12.00.00.000000000

     

     

    To_Integer(To_Char(To_Date(testdate,"DD/MM/YYYY 24HH:MM:SS"), "DDMMYYY24HHMMSS"))

     

    Hope this helps.

     

    Kindly let me know if this helps.

     

    Thanks

    fahad

  • sasi ramesh Master 1,110 posts since
    Oct 2, 2010
    Currently Being Moderated
    Nov 14, 2012 2:47 AM (in response to SRI SRI)
    Date Conversion from string to number

    Hi Srini,

     

    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

     

     

    Regards

    Sasiramesh

More Like This

  • Retrieving data ...

Bookmarked By (0)