date range last Monday to last Saturday

Six Stars

date range last Monday to last Saturday

Hi everyone I have an issue:

I have a table called orders and there's a column called "order_date" in datetime format.
Now I need a query which will select always the date range from last Monday 00:00:01 to last Saturday 23:59:59.

How I can build this query and which elements do I need? tMap only?


Thanks for any advice.

Highlighted
Employee

Re: date range last Monday to last Saturday

Hi,

 

     You can implement by importing corresponding java Library to Talend code using tLibraray code and run the below code in the link.

 

https://stackoverflow.com/questions/12783102/how-to-get-the-last-sunday-before-current-date

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

 

 

Employee

Re: date range last Monday to last Saturday

Hi,

I have written a simple java code in tjava component which always fetches the date of last Monday and Saturday. This can be assigned to context variables and can be used in where condition while fetching data. But make sure to typecast it to just date (without timestamp) on both sides of where condition. So that no records will be missed due to difference in timestamp.

 

int num_days = TalendDate.getPartOfDate("DAY_OF_WEEK",TalendDate.getCurrentDate());
System.out.println(TalendDate.addDate(TalendDate.getCurrentDate(),-num_days-5,"dd")); // Last Monday date
System.out.println(TalendDate.addDate(TalendDate.getCurrentDate(),-num_days,"dd")); // Last Saturday date

 

Regards,

Pratheek Manjunath

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog