topic Re: Query in tmssqlInput to retrieve data on date conditon in Talend Studio

Query in tmssqlInput to retrieve data on date conditon

Anonymous — Sat, 16 Nov 2024 11:08:38 GMT

Hi,
I have requirement where i have to query data on the table based on the date condition and write it into a csv file.
I have used the where conditon in query like :
"where modification_time<'Talend.getCurrentDate()-30'"
but this is giving me an error
"Conversion failed when converting the varchar value 'TalendDate.getCurrentDate()' to data type int."

please help as this is urgent.
Thanks,
Tulasi

Re: Query in tmssqlInput to retrieve data on date conditon

Jcs19 — Tue, 14 Jul 2015 08:08:23 GMT

"where modification_time<'Talend.getCurrentDate()-30'" has a bad synthax

I tihnk its "where modification_time < ' " + Talend.getCurrentDate() - 30 + " ' " (see the single quote)
Also Im not sure if you can do Talend.getCurrentDate() - 30

Re: Query in tmssqlInput to retrieve data on date conditon

Anonymous — Tue, 14 Jul 2015 11:51:01 GMT

HI,
i have tried the below condition it is not working.
I have requirement where i have to retrieve all the data from database based on date condition to get the no of days between today's date and the modified date.
Can you please help me with the comparision.
Thanks,
Tulasi

Re: Query in tmssqlInput to retrieve data on date conditon

Anonymous — Tue, 14 Jul 2015 12:18:21 GMT

Are you wanting to return data based on the number of days from the current date? If so, there is absolutely no need make it complicated by using Java to build your SQL query. The example below is a VERY basic example that you should be able to extrapolate from. This returns the number of days between the SYSDATE (current date) and the date specified.

select datediff(SYSDATE(), '2010-04-12');

Re: Query in tmssqlInput to retrieve data on date conditon

Jcs19 — Tue, 14 Jul 2015 12:37:47 GMT

If you want to use Java instead of sql then use :
TalendDate.addDate(TalendDate.getCurrentDate(),-30,"dd")
or
TalendDate.addDate(TalendDate.getCurrentDate(),-1,"MM") -> -1 month
it will returns a date that you MUST convert into a string, see formatDate function
then insert the string into your query

Re: Query in tmssqlInput to retrieve data on date conditon

Anonymous — Tue, 14 Jul 2015 13:14:36 GMT

Hi,
I have tried to check the difference between the two dates if it is >90 days.
but i am not able to compare them using the > or < operators.
Is there any alternative for this.
Thanks,
Tulasi

Re: Query in tmssqlInput to retrieve data on date conditon

Anonymous — Tue, 14 Jul 2015 13:41:31 GMT

Are you trying to do this in Java or in SQL? That is an important question which you must answer for help. The SQL that is used in the tMSSQLInput (I've just realised you are using MS SQL and not MySQL which my previous answer was aimed at) is basically a Java String. You *can* make this dynamic and change hardcoded values based on the data you are working with in Java. This will produce SQL code engineered specifically for that particular datarow. That is what jcs19 is talking about. This is a really effective method of working IF (and only IF) you can write SQL and Java well enough to be able to write one code with the other. I sense from your question that you are not an experienced SQL developer and/or not an experienced Java developer.
We can get round this, but you must give us all of the information. It might be that it is easier for you to write this entirely in SQL or it could be that Java is required. The first thing to do is to figure out the base SQL that will be needed and then we can help you to parameterise it to make it dynamic (if you need to do that).
So what is it exactly that you are trying to do?

Re: Query in tmssqlInput to retrieve data on date conditon

Jcs19 — Tue, 14 Jul 2015 13:56:41 GMT

hi,
(TalendDate.diffDate(myDate,myDate2,"dd")>90)?"Date is superior":"Date is inferior"
What are you trying to do ? changing your query ? or filtering

Re: Query in tmssqlInput to retrieve data on date conditon

Anonymous — Tue, 14 Jul 2015 14:24:33 GMT

HI,
We are actually trying to archive the data from database of past 120 days from today and write it into a csv file.
I am querying on a table using the tmssqlInput--->tmap--->tfileoutputdelimited.
In tmssqlInput i am writing a select query based on the condition:
select * from table where modified_date < today's date-120
where modified_date is column in the table and today's date is currentdate.
Now i am not able to use the '<' condition properly.

Re: Query in tmssqlInput to retrieve data on date conditon

Anonymous — Tue, 14 Jul 2015 14:59:01 GMT

Try something like this....

select *
from table
where DATEDIFF(DD,modified_date, getdate())<=120 AND DATEDIFF(DD,modified_date, getdate())>=0

Re: Query in tmssqlInput to retrieve data on date conditon

Jcs19 — Tue, 14 Jul 2015 14:59:06 GMT

HI,
We are actually trying to archive the data from database of past 120 days from today and write it into a csv file.
I am querying on a table using the tmssqlInput--->tmap--->tfileoutputdelimited.
In tmssqlInput i am writing a select query based on the condition:
select * from table where modified_date < today's date-120
where modified_date is column in the table and today's date is currentdate.
Now i am not able to use the '<' condition properly.

< and > should work on your sql query
I'm pretty sure that you made a mistake on today's date-120
At the end, your SQL Query should be like "select * from table where modified_date < '2015-05-02';"

I wanted you to find the solution alone, it's so much more exciting

Two solutions
SQL: "Select * from table WHERE modified_date > DATEADD( dd, -120, getdate()) ;"

Java: "Select * from table where modified_date < ' " + TalendDate.formatDate("yyyy-MM-dd",TalendDate.addDate(TalendDate.getCurrentDate(),-120,"dd")) + " ' ;"