cancel
Showing results for 
Search instead for 
Did you mean: 

How can I set a where clause for getting the date dynamically in XSJS ?

Former Member
0 Kudos

Hi ,

I am facing the issue of getting some data based on a date filter.

Hence, I need to provide the date in the where clause. I'v tried these till now ---

1. var query = 'SELECT DISTINCT \"StationId\",\"Status\" FROM \"***********************************************\" WHERE \"StartDate\" = \'CURRENT_DATE\'';

2. var query =

'SELECT DISTINCT \"StationId\",\"Status\" FROM \"***********************************************\" WHERE \"StartDate\" = \'GetDate()\'';

The other option was to write a new xsjs to get the date as output & modify it accordingly. I did that as well.

But, the problem lies in passing a variable within a where clause/ getting the date dynamically in where clause.

Please, suggest a solution.

Thanks a Lot,

Sudipta

Accepted Solutions (0)

Answers (1)

Answers (1)

Robert_Russell
Contributor
0 Kudos

Hi Sudipta,

As already mentioned I think you are better finding a new more relevant tag for the question.

My suggestion for the answer however is to use a prepared statement such as

select ? from TABLE where COLUMN_NAME = ?

pstmt.setString(1, select_column);
pstmt.setString(2, where_clause);

To set the date dynamically for the SQL a modified hello user type example below.

Regards

Robert

$.response.contentType = "text/html";
//var output = "Hello, " + $.session.getUsername() + new Date + " <br><br>";
var output = "Hello, " + $.session.getUsername() + " Full Date is " +  new Date() + ' and the day of month is ' +  new Date().getDate() + " <br><br>";
var conn = $.db.getConnection();
var pstmt;


var query =   'Select ?  FROM DUMMY';  
pstmt = conn.prepareStatement(query);  
pstmt.setString(1, output);


var rs = pstmt.executeQuery();
if (!rs.next()) {
	$.response.setBody( "Failed to retrieve data" );
	$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
}
else { 
	output = output + "This is the response from my SQL. The current user is: " + rs.getString(1);
}
rs.close();
pstmt.close();
conn.close();
$.response.setBody(output);