Skip to main content

Convert Numeric Value to Date Format



                                                                                                                                                                      
Scenario:
Suppose you are importing a flat file emp.csv and hire_date colummn is in numeric format, like 20101111 .Our objective is convert it to date,with a format 'YYYYMMDD'.
 Source
  EMPNO       HIRE_DATE(numeric)         
  -------            -----------
     1                20101111
     2                20090909
Target
EMPNO            HIRE_DATE (date)        
 ------                   -----------
     1                   11/11/2010
     2                    09/09/2009

Solution:

  1. Connect SQF to an expression.
  2. In expression make hire_date as input only and make another port hire_date1 as o/p port with date data type.
  3. In o/p port of hire_date write condition like as below
          TO_DATE(TO_CHAR(hire_date),’YYYYMMDD’)


Comments

  1. aishaDecember 27, 2011 at 2:22 PM

    I enjoyed your post. It’s a lot like college – we should absorb everything we can but ultimately you need to take what you’ve learned and apply it.Nice man. Extremely informative post. I'll be sure to pass this along to my friends.
    Suzuki Esteem AC Compressor

    ReplyDelete
  2. AnonymousDecember 23, 2015 at 5:30 PM

    The data warehouse interview questions are all related and here in this particular website user can get the chance to learn more about interview questionnaires as well as topics. The blog here covers all important topics related to the interview question and other necessary areas. The questionnaires are all prepared and tabulated by professional informatica interview questions and answers for experienced.

    ReplyDelete

Post a Comment

Popular posts from this blog

Informatica Quiz: Set 1

                                                                                                                                                                       Quiz: Informatica Set 1 Which one is not correct about filter transformation? Explanation: Filter generally parses single condition. For multiple condition we can use router Act as a 'where' condition Can't passes multiple conditions Act like 'Case' in pl/sql (wrong) If one record does not match condition, the record is blocked Can we calculate in aggrigator ? Explanation: No Yes (correct) Which one is not a type of  fact? Explanation: Semi-aditive Additive Confirm fact Not additive (wrong)  Which one is not a type of dimension ? Explanation: Conformed dimension Rapidly changing dimension (correct) Junk dimension Degenerated dimension Which of these not correct about Code Page? Explanation: A code page contains encodin
2 comments
Read more

Function : NVL2 and COALESCE

NVL2 The NVL2 function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter. The following query shows NVL2 in action. SQL> SELECT * FROM null_test_tab ORDER BY id;           ID COL1        COL2        COL3        COL4 ---------- ---------- ---------- ---------- ----------          1 ONE         TWO         THREE       FOUR          2             TWO         THREE       FOUR          3                         THREE       FOUR          4                        THREE       THREE   4 rows selected. SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;                  ID OUTPUT ---------- ----------                  1 TWO                  2 THREE                  3 THREE                  4 THREE   4 rows selected.   SQL> COALESCE The COALESCE function was introduced in Oracle 9i
Post a Comment
Read more

Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose.

                                                                                                                                                                       Scenario: Load the session statistics such as Session Start & End Time, Success Rows, Failed Rows and Rejected Rows etc. into a database table for audit/log purpose. Solution: After performing the below solution steps your end workflow will look as follows: START => SESSION1 => ASSIGNMENT TASK => SESSION2 SOLUTION STEPS SESSION1 This session is used to achieve your actual business logic. Meaning this session will perform your actual data load. It can be anything File Table. à File or Table à Table, File à WORKFLOW VARIABLES Create the following workflow variables. => $$Workflowname => $$SessionStartTime => $$SessionEndTime => $$TargetSuccessrows => $$TargetFailedRows ASSIGNMENT TASK Use the Expression tab in the Assignment Task and assign as follo
Post a Comment
Read more