Query in tmssqlInput to retrieve data on date conditon

[Anonymous]

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

[Jcs19]

"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

[Anonymous]

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

[Anonymous]

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');

[Jcs19]

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

[Anonymous]

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

[Anonymous]

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? 

[Jcs19]

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

[Anonymous]

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.

[Anonymous]

Try something like this....

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