Date Validation-SQL Query or Mapping

[justin_santhanam]

Dear Friends,

I'm using sender JDBC adapter and fetching data.

Let say the table structure is Name,ID,Datetime.

I want to select the query and the resultset must be in the following fomat

I want to check the validation that if Datetime value is 2 weeks greater than current date return YES, else return NO

Is the above condition possible while selecting from the database itself?

If we can't achieve it using query, then I've to go for Mapping. Can you guys tell me the logic to implement the same.

<Rows>

<Name>J</Name>

<ID>126376</ID>

<DateTime>YES</DateTime>

</Rows>

<Rows>

<Name>P</Name>

<ID>126372</ID>

<DateTime>YES</DateTime>

</Rows>

<Rows>

<Name>S</Name>

<ID>126334</ID>

<DateTime>NO</DateTime>

</Rows>

If the above requirement is not clear, please let me know.

Thanks for all of your valuable time!!!

Best regards,

raj.

[Former Member · Accepted solution]

Raj,

If you are doing in the database then first you have to compare the datetime field with the current date and then update the datetime field to YES or NO. I think technically this may be possible. But would recommend to take any DBA help for this.

But in XI if you have the datetime field then I think using UDF you can compare and send YES or NO as an output from it. So depending upon this value use createif function whether to send the record or not. Do a search in SDN for this UDF.

Regards,

---Satish

[justin_santhanam · Accepted solution]

Satish,

Thanks for your reply. I don't want to change the DB. I need to achieve it using UDF. Let me play sometime with code and update the thread. In th mean time if you guys have some logic , please reply!!

Best regards,

raj.

[Former Member · Accepted solution]

Hi Raj !

With the query I've posted you don't need to make any changes to the DB, it is just a SELECT query, that returns each it is executed, the result of comparing the current date (getdate) with the date saved in the datetime fields. If you give me your dbms name and version, maybe I can give the exact query.

Using UDF, you can use a simple UDF that uses java to solve the same calculation.

Regards,

Matias.

[Former Member · Accepted solution]

Raj,

Check this thread where you have a very near sample code:

Regards,

---Satish

[justin_santhanam · Accepted solution]

Satish & Matias,

Thanks a lot for ur valuable time and help.

@Matias I tried to query with the SQL , but I failed since its throwing exception always.

@Satish: I wrote UDF and achieved the same.

<b>Code:</b>

//Sample input date: 2005-06-16 14:33:21.0

int year=Integer.parseInt(time.substring(0,4));

int month=Integer.parseInt(time.substring(5,7))-1 ;

int date=Integer.parseInt(time.substring(8,10));

int hour=Integer.parseInt(time.substring(11,13));

int min=Integer.parseInt(time.substring(14,16));

int sec=Integer.parseInt(time.substring(17,19));

Calendar c1 = Calendar.getInstance();

Calendar c2 = Calendar.getInstance();

c1.set(year,month,date,hour,min,sec);

c1.add(Calendar.DAY_OF_MONTH,14);

if(c2.before(c1))

{

return "NO";

}

else

{

return "YES";

}

Thanks friends!!!

Best regards,

raj.

[Former Member · Accepted solution]

Hi Raj !

Great you solved. Are you using MS SQL Server? could you post the exception it is throwing??

Thanks.

Regards,

Matias.

[justin_santhanam · Accepted solution]

Matias,

I'm using MS-SQL Server. Its throwing some exception like " syntax error near <b>></b> ". So I left it and used the java code. But need to educate myself in oder to get it work , not for this interface atleast for any future interface developments

Best regards,

raj.

[Former Member]

Hi Raj !

I think you could write a SELECT query like this:

SELECT name,id,(CASE WHEN datediff(d,getdate(),datetime) >14 THEN "YES" ELSE "NO" END CASE) as datetime FROM table or create a view (this code is for MS SQL)...I think mapping is not required.

Regards,

Matias.

[justin_santhanam]

Matias,

Can u give me the exact syntax, its throwing exception.

Best regards,

raj.

[Former Member]

Hi Raj !

Try this (MS SQL Server only):

SELECT name,id, CASE WHEN datediff(DAY,getdate(),datetime) >14 THEN "YES" ELSE "NO" END as datetime FROM replace with your table name, also check that the values in the "datetime" field are in the correct format or cast it accordingly.

Regards,

Matias.