global.skip_navigation

    Log In

    Forgot your password?
    Want to become a partner? Sign Up
marketplace logo
6392 global.views 1 forum.thrd.reply.gtitle forum.thrd.last_post.linkglobal.colon Jul 30, 2015 11:58 AM forum.thrd.by.link Ravikumar Sanjeevi
mod.post_in_moderation.text

Mar 16, 2017 12:03 AM

How to convert and load a string into the date/time field

Hi,

 

I'm facing an issue with the date field. I have to load the date column from file to table. I'm reading that filed as string and loading into the date/time filed.

My sample input data from flat file is '20150619 12:43 AM' i have to load this data into any of the date format. Here are the conditions that i have used to load the data:

 

> TO_CHAR(To_DATE(DATE))

> IIF(IS_DATE(DATE,'YYYYMMDD HH12:MMPM'),TO_DATE(DATE,'YYYYMMDD HH12:MM:SS'),null)

 

 

These conditions didnt workout and loading nulls into the target table. Please suggest me where im doing worng.

 

Thanks in advance.

  • Ravikumar Sanjeevi Expert 566 forum.thrd.posts_since.label
    May 7, 2011
    mod.post_in_moderation.text
    Jul 30, 2015 11:58 AM global.left_paren global.right_paren
    How to convert and load a string into the date/time field

    Hi Pawan,

     

    In any ETL tool for date we need follow these steps

    1) Conver the string to incoming date

    2) Now format the converted date to required format

    3) Then convert it back to date as per new format

     

    i.e

    v_B=TO_DATE('20150619 12:43 AM','YYYYMMDD HH12:MM AM')

    v_C=TO_CHAR(v_B,'YYYYMMDD HH12:MM:SS')

    v_D=TO_DATE(v_C,'YYYYMMDD HH12:MM:SS')

     

    Regards,

    Sanjeevi

forum.thrd.actions.gtitle

mrlkthis.more_like_this.gtitle

  • mrlkthis.retrieve_data.text