Variables Function and Built in Variables. - Playing with Date and Time

Please subscribe on my YouTube Channel.



How to Set Mapping Variable LastRunDate

SETVARIABLE($$LASTRUNDATE,SESSSTARTTIME)

Set Max value 

SETMAXVARIABLE($$LastExtractionDate, Start_Date)


Convert Sysdata to Informatica Date/Time

TO_DATE(to_char(sysdate, 'MM-DD-YYYY'), 'MM-DD-YYYY')

String To Date

TO_DATE(Publication_date,'DD-MM-YYYY')

Get System Data in Informatica.

SYSDATE

Get year from the date within Informatica.

GET_DATE_PART(var_prev_Start_Date_Tier,'YYYY')
GET_DATE_PART(var_prev_Start_Date_Tier,'MM')

Add second to date.

ADD_TO_DATE(lastmoddate, 'SS',-1)

Truncate second of a DateTime:

TRUNC(DateTime, 'SS')

Truncate TimeStamp from Sysdate.

TRUNC(SYSDATE) will remove Timestamp

TO_CHAR(SYSDATE,'YYYYMMDD') will format the required format

Add Day to Date

ADD_TO_DATE($$LASTRUNDATE, 'DD', -1)
ADD_TO_DATE($$LASTRUNDATE, 'DD', $$DAYCONST)


Extract TimeStamp from 

substr(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'), 12, 19)


Case in Informatica

DECODE(ID,1,'IDis1',2,'IDis2',3,'IDis3',4,'IDis4','IDisNotInAbove-i.e. 1to4')

UpdateTimestamp

SYSTIMESTAMP('MS')


Unconnected Lookup Expression

:LKP.SC_LKP_RUN_REG_KEY($PMFolderName,$PMSessionName)


Unconnected Stored Procedure Invoke

on variable port which will store return value use.

:SP.sp_ProcessTempAssociation()



DatePart

Sets one part of a Date/Time value to a value you specify. With SET_DATE_PART, you can change the following parts of a date:

Year(YY) : Month(MONTH) : Day (DD): Hour(HH) : Minute(MI) : Seconds(SS) : Milliseconds(MS): Microseconds(US) :  Nanoseconds(NS)

SET_DATE_PART( date, format, value )

SET_DATE_PART(
        SET_DATE_PART(
           SET_DATE_PART(DATE_INSERTED, 'HH', 23)
                                      , 'MI', 59)
                            , 'SS', 59)


Subtract 2 Dates.

DATE_DIFF function

Returns the length of time between two dates. You can request the format to be years, months, days, hours, minutes, seconds, milliseconds, microseconds, or nanoseconds. The Data Integration Service subtracts the second date from the first date and returns the difference.

Syntax

DATE_DIFF( date1, date2, format )

The following table describes the arguments for this command:

DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'HH' )  to Get difference in hours

DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'D' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DD' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DDD' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DY' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'DAY' )

DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MM' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MON' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'MONTH' )

DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'Y' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'YY' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'YYY' )
DATE_DIFF( DATE_PROMISED, DATE_SHIPPED, 'YYYY' )

refer below link for more documentation

https://docs.informatica.com/big-data-management/big-data-streaming/10-2-1/transformation-language-reference/functions/date_diff.html