Data filtering using date parameter from Query String

asked on January 18, 2016 11:55

I am working on one project where I have simple custom table data list webpart. I need to filter this data using query string where date is submitted. e.g: domain.com?date=2015-01-23. I need where condition macro with the following logic. If date parameter is passed than filter otherwise list unfiltered data. How can I achieve this? I have tried following code:

{% result = ""; if (QueryString.startDate != null) { result = "StartDate = " + QueryString.startDate|(handlesqlinjection)true; } else { result = "StartDate >" + "1900-01-01".ToString(); } return result; %}

Thank you

Correct Answer

answered on January 22, 2016 06:57

I found solution. {% result = ""; queryStartdate = FormatDateTime(QueryString.startDate, "yyyy-MM-dd"); if (queryStartdate!= null) { result = "CAST(StartDate AS date) = '" + queryStartdate+ "'";} return result;|(handlesqlinjection)false|(identity)GlobalAdministrator%}

You should cast as date in string and works fine.

0 votesVote for this answer Unmark Correct answer

Recent Answers

David te Kloese answered on January 18, 2016 13:56 (last edited on December 10, 2019 02:30)

Hi,

I assume you placed this macro in the Where clause of your webpart?

The macro seems ok.

The problem is you need to add single quotes to the result date. But the macro itself excludes the single quotes by adding an other quote.

So by making sure the query value is a date you can use the following macro:

{% result = "";
queryStartdate = FormatDateTime(QueryString.startDate, "yyyy-MM-dd");
if (queryStartdate!= null)
{ result = "StartDate > '" + queryStartdate+ "'";}
return result;|(handlesqlinjection)false|(identity)GlobalAdministrator%}

Might want to optimize it a bit, but this will get you going.

I didn't use your else statement since if left blank everything will be selected by default. If you need it you can just add it.

EDIT:

extra tip, did you know you can debug your macro's (and SQL and much more). If you go to Settings > System > Debug you can enable these settings:

screen

This will give you extra info if your macro and query actually work!

Greets,

David

1 votesVote for this answer Mark as a Correct answer

answered on January 19, 2016 15:26 (last edited on December 6, 2018 08:31)

Thank you David very much. It works fine for me. Great answer and fast response.

0 votesVote for this answer Mark as a Correct answer

answered on January 21, 2016 15:09 (last edited on December 10, 2019 02:30)

Hello guys, I've got a one question about this post. David posted this code:

{% result = ""; queryStartdate = FormatDateTime(QueryString.startDate, "yyyy-MM-dd"); if (queryStartdate!= null) { result = "StartDate > '" + queryStartdate+ "'";} return result;|(handlesqlinjection)false|(identity)GlobalAdministrator%}

and it works perfectly fine, but what should I do if I want to cast "StartDate" as date, because my database field is datetime type and query string that is passed is date. Please help me to solve this problem.

thank you in advance

0 votesVote for this answer Mark as a Correct answer