How to search for a date less than or greater than X

Question

This is the code I'm using to create a custom report that filters by a date range. The first criteria works fine, however the second and third do not. It throws a syntax error. Can anyone tell me why?

string cmd = "SELECT PURCHASE_ORDER.cid, PURCHASE_ORDER.deptid, PURCHASE_ORDER.procid, PURCHASE_ORDER.purchase_order_no, PURCHASE_ORDER.requesition_no, PURCHASE_ORDER.contract_no, PURCHASE_ORDER.purchase_order_date, COMPANY.company_name,  DEPARTMENT.department_name, PROCUREMENT_METH.method FROM PURCHASE_ORDER INNER JOIN PROCUREMENT_METH ON PURCHASE_ORDER.procid = PROCUREMENT_METH.procid INNER JOIN COMPANY ON PURCHASE_ORDER.cid = COMPANY.cid INNER JOIN DEPARTMENT ON PURCHASE_ORDER.deptid = DEPARTMENT.deptid WHERE PURCHASE_ORDER.deptid = DEPARTMENT.deptid";

// Check Criteras
if (!string.IsNullOrWhiteSpace(ddlDepartment.Text) && !ddlDepartment.Text.Equals("0"))
   cmd += " AND PURCHASE_ORDER.deptid LIKE '%" + ddlDepartment.Text + "%' ";

if (!string.IsNullOrWhiteSpace(txtfromdate.Text))
   cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txtfromdate.Text + "' ";

if (!string.IsNullOrWhiteSpace(txttodate.Text))
   cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txttodate.Text + "' ";

You need to parse the contents of the textbox using `DateTime.Parse`, ideally `DateTime.TryParse` to validate input. — adaam, Jul 26 '14 at 00:13

score 4 · Answer 1 · edited May 23 '17 at 11:57

4

It really helps to actually read through your generated SQL, and actually read the syntax error messages.

Let's say txttodate.Text gives the value "25-Jul-2014". Here's what your code generates for that:

AND PURCHASE_ORDER.purchase_order_date '<25-Jul-2014'

And the error message SQL Server generates in that situation:

Error 102: Incorrect syntax near '<25-Jul-2014'.

That's because your < is inside the string. Try this way instead, it'll work much better:

cmd += "AND PURCHASE_ORDER.purchase_order_date < '" + txttodate.Text + "' ";

And while we're on the topic of making this better, use parameters.

edited May 23 '17 at 11:57

Community

1
1

answered Jul 26 '14 at 00:24

Corey

15,524
2
35
68

That is correct. I fixed it in my answer. Thank you – Victor_Tlepshev Jul 26 '14 at 00:26
@vt146911 In general, unless you're embarking on a teaching exercise, you don't answer your own questions. If there's an answer that's correct, accept that answer. – Corey Jul 26 '14 at 00:29
I did not see your answers until I submitted mine. – Victor_Tlepshev Jul 26 '14 at 00:31

adaam · Answer 2 · 2014-07-26T00:24:16.817

2

If your purchase_order_date database field is of type DATETIME then you will need to parse the contents of the textbox into a C# Date object and then ToString() the Date object to the SQL standard date format.

Rudimentary:

DateTime time = DateTime.Parse(txttodate.Text.Trim().Replace("'","''"));
cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + datetime.ToString("yyyy-MM-dd") + "' ";

Better:

DateTime dateTime,dateTime2;

if (DateTime.TryParse(txttodate.Text.Trim().Replace("'","''"), out dateTime1) && DateTime.TryParse(txtfromdate.Text.Trim().Replace("'","''"), out dateTime2))
{
    cmd += "AND PURCHASE_ORDER.purchase_order_date BETWEEN + '" + dateTime1.ToString("yyyy-MM-dd") + "' AND '" + dateTime2.ToString("yyyy-MM-dd") + "'";
}

Also I think you are probably looking to use the BETWEEN SQL operator to determine any purchase_order_dates between the FROM date and the TO date.

edited Jul 26 '14 at 00:24

answered Jul 26 '14 at 00:18

adaam

3,700
7
27
51

That would work as well. But in my case it is fine. Is just the statement was not spaced that is why it kept failing. Sorry about that – Victor_Tlepshev Jul 26 '14 at 00:25
@vt146911 You are opening yourself up to all kinds of dangers by not validating user input (yes including those inputted through the calendar control) – adaam Jul 26 '14 at 00:26
Simply use parametrized queries... While your suggestion is marginally better than original code it still bad idea. – Alexei Levenkov Jul 26 '14 at 00:32
@AlexeiLevenkov I am aware of that - but I know he isn't going to implement parameterized queries so a hackjob solution will be better than nothing ;) – adaam Jul 26 '14 at 00:35

score -1 · Answer 3 · answered Jul 26 '14 at 00:23

-1

I fixed it. the problem was a space after

cmd += "AND PURCHASE_ORDER.purchase_order_date '<" + txtfromdate.Text + "'

like this:

cmd += " AND PURCHASE_ORDER.purchase_order_date <'" + txtfromdate.Text + "'

answered Jul 26 '14 at 00:23

Victor_Tlepshev

478
6
19

I would put a trailing space on the first line, and remove the leading space from the filter lines. – Dave Cousineau Jul 26 '14 at 00:26
Where you put your spaces doesn't make any difference. The problem was that you were putting the operator inside the string. – Corey Jul 26 '14 at 00:27
2

-1: This is not very good suggestion - please don't follow it. Use parametrized queries instead. – Alexei Levenkov Jul 26 '14 at 00:34

How to search for a date less than or greater than X

3 Answers3