top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Infomatica ISO-8601 date

0 votes
468 views

I am using Informatica Powercenter and need to insert strings/dates similar to this one from a flat file to Oracle.

2013-07-23T13:55:33.0000000-04:00

In Oracle the solution is
to_timestamp_tz(‘2013-07-23T13:55:33.0000000-04:00’,’YYYY-MM-DD”T”HH24:MI:SS.FF7TZR’)

What is the best way to convert this string/date so that Informatica will interpret it correctly? None of the Informatica expressions I see handle the "-4:00" correctly.

posted Sep 16, 2014 by Amit Sharma

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

If you do not need the timezone information, you can use substr to remove the -04:00 part and convert the rest to date/time using TO_DATE function.

answer Sep 18, 2014 by Shweta Singh
Similar Questions
+1 vote
Blank line in between each records in infomatica

I have a workflow which writes data from a table into a flat file. It works just fine, but I want to insert a blank line in between each records. How can this be achieved ?

+2 votes
Converting time stamp in informatica to date

I want to display the time stamp in following format '2011-04-22 10:41:57.000' to date as '04/22/2011' but when it convert it to following form TO_DATE(TO_CHAR(Date), 'MM/DD/YYYY HH24:MI:SS') it is displaying as null.

I am planning to use substring after the conversion

Can someone please tell me where i am going wrong?

0 votes
Oracle Prior date Query

I am having problem in fetching the distinct previous reportign date from my date dimension. I need to populate 12 previous reportign dates(distinct) for a given calendar date. This reporting date is populated on caertian business rules. For instnace 

CAL_DATE    RPT_DATE
22-Nov-14   24-Nov-14
23-Nov-14   24-Nov-14
24-Nov-14   24-Nov-14
25-Nov-14   25-Nov-14
26-Nov-14   26-Nov-14
27-Nov-14   1-Dec-14
28-Nov-14   1-Dec-14
29-Nov-14   1-Dec-14
30-Nov-14   1-Dec-14
1-Dec-14    1-Dec-14
2-Dec-14    2-Dec-14
3-Dec-14    3-Dec-14
4-Dec-14    4-Dec-14
5-Dec-14    5-Dec-14
6-Dec-14    8-Dec-14
7-Dec-14    8-Dec-14

o/p I am looking for is 

DAT_DATE_   RPT_DT  PRIOR1  PRIOR12 PRIOR3  PRIOR4  PRIOR5  PRIOR6
4-Dec-14    3-Dec-14    2-Dec-14    1-Dec-14    26-Nov-14   25-Nov-14   24-Nov-14   21-Nov-14
3-Dec-14    2-Dec-14    1-Dec-14    26-Nov-14   25-Nov-14   24-Nov-14   21-Nov-14   20-Nov-14
The query i used for this is 
select DAT_DATE_DT,dat_sls_wrk_rpt_dt,Prior1,Prior2,Prior3,Prior4,Prior5,Prior6,Prior7,Prior8,Prior9,Prior10  from (
  select DAT_DATE_DT,
  dat_sls_wrk_rpt_dt,
  lag(dat_sls_wrk_rpt_dt,1)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior1,
  lag(dat_sls_wrk_rpt_dt,2)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior2,
  lag(dat_sls_wrk_rpt_dt,3)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior3,
  lag(dat_sls_wrk_rpt_dt,4)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior4,
  lag(dat_sls_wrk_rpt_dt,5)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior5,
  lag(dat_sls_wrk_rpt_dt,6)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior6,
  lag(dat_sls_wrk_rpt_dt,7)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior7,
  RANK 
FROM
  ( SELECT DISTINCT B.DAT_DATE_DT,
  A.dat_sls_wrk_rpt_dt,
  dense_rank() over (partition by B.DAT_DATE_DT order by A.dat_sls_wrk_rpt_dt DESC) RANK
  FROM EWT_DATE_DIM1 A,
    EWT_DATE_DIM1 B
  WHERE A.dat_sls_wrk_rpt_dt < B.dat_sls_wrk_rpt_dt
  AND B.DAT_DATE_DT in (trunc(sysdate-15),trunc(sysdate-16) )
  )
WHERE RANK <12)
where RANK=1
ORDER BY 2 DESC

Can anyone think something simpler than this. As there can be many dates for the historic loads and I want to fine tune this. Also, i need this to be done in informatica.

+3 votes
Informatica - Get Date of Monday of a Week number

How to calculate the Monday date of a week using the week number in Informatica?!

+2 votes
Informatica: Pass date value from one session to another session in the same workflow

I have two sessions I a workflow like below

workflow1->session1->session2

I have a join_date column in a table in Mapping1 , in session1. I want to pick this join_date value and pass to mpping2/session2

If join date value changes in the table in session1 then the same value should pick and pass to session2. I will use this date value in a query in session2 .

Please suggest how to achieve this?

...