Check date in a particular date range using tmap filter

Six Stars

Check date in a particular date range using tmap filter

How do I check if a Date exists within a specific date range?

I am joining a pipeline which comes from jdbcInput--> tmap1--> tMap_join -->tOutputExcel
For tMap_Join : I am joining a SQL DB table with tMap1 out result. all dates column are in 'yyyy-MM-dd' format.

I need to check : tmap1.st_dat  >= sqlTable.st_dat AND tmap1.end_dat  <= sqlTable.end_dat

in tmap_join filter area.

 

How do i achieve this??
Attaching screenshot for this.


Regards,
Mohit

Accepted Solutions
Six Stars

Re: Check date in a particular date range using tmap filter

I have check the data and write condition below for this:
(TalendDate.compareDate(tmap1.st_dat, sqlTable.st_dat ==1 || TalendDate.compareDate((tmap1.st_dat, sqlTable.st_dat) ==0) &&
(TalendDate.compareDate(tmap1.end_dat, sqlTable.end_dat) ==-1 || TalendDate.compareDate((tmap1.end_dat, sqlTable.end_dat)) ==0)

 

We can understand TalendDate.compareDate by this :
Date1 < Date2   :  Returns -1
Date1 = Date2   :  Returns 0
Date1 > Date2   :  Returns 1

 

Above condition works for me.


Regards,
Mohit

All Replies
Moderator

Re: Check date in a particular date range using tmap filter

Hello,

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Six Stars

Re: Check date in a particular date range using tmap filter

I have check the data and write condition below for this:
(TalendDate.compareDate(tmap1.st_dat, sqlTable.st_dat ==1 || TalendDate.compareDate((tmap1.st_dat, sqlTable.st_dat) ==0) &&
(TalendDate.compareDate(tmap1.end_dat, sqlTable.end_dat) ==-1 || TalendDate.compareDate((tmap1.end_dat, sqlTable.end_dat)) ==0)

 

We can understand TalendDate.compareDate by this :
Date1 < Date2   :  Returns -1
Date1 = Date2   :  Returns 0
Date1 > Date2   :  Returns 1

 

Above condition works for me.


Regards,
Mohit
Four Stars

Re: Check date in a particular date range using tmap filter

How to filter date something like -- if today == Monday then filter from current date - 2 days (Sat and Sun) else filter date from current date

Forteen Stars

Re: Check date in a particular date range using tmap filter

@Pradeep,you need to use the below way.

 

TalendDate.getPartOfDate("DAY_OF_WEEK",TalendDate.parseDate("dd-MM-yyyy HH:mm:ss", "13-10-2010 12:23:45"))==2 ?TalendDate.addDate(TalendDate.getCurrentDate() ,-1,"dd") :TalendDate.getCurrentDate()

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Four Stars

Re: Check date in a particular date range using tmap filter

@manodwhb Thanks for the help however how would this help in terms of range filter in Date?

as we have not mentioned any range like BETWEEN query!

 

Example

I have set of records

12-01-2018

12-02-2018

12-03-2018

12-04-2018

If I run my job today (Monday) output should have 3 records 

12-01-2018

12-02-2018

12-03-2018

if i run it tomorrow then the output will be 12-04-2018

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch